package com.augurit.gzcgwsp.rqqyjyxk.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.Query;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.Page;
import org.springside.modules.orm.PropertyFilter;

import com.augurit.common.system.web.auth.form.LoginUserForm;
import com.augurit.common.util.HqlUtils;
import com.augurit.common.util.PageUtils;
import com.augurit.common.util.lang.StringUtils;
import com.augurit.gzcgwsp.commonex.util.lang.DateUtilsEX;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkCyryzkdjb;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkGdyjgqjgdjb;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkPzrqgyzdjb;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkQysbclmc;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkRqjszdjb;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkRqjszrypbqk;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkRqjszzysb;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkRqqxdylb;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkRqqxsbylb;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkRqqxssdjb;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkRqssdjb;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkRypbqk;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkZyaqjsglryzk;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkGdyjgqjgdjbService;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkImportservice;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRqssylbService;
import com.augurit.gzcgwsp.excelimportexport.util.ExcelUtil;
import com.augurit.gzcgwsp.excelimportexport.util.FileUtil;
import com.augurit.gzcgwsp.excelimportexport.util.GetFileName;
import com.augurit.gzcgwsp.rqqyjyxk.convert.YwRqQyjyxkConvertor;
import com.augurit.gzcgwsp.rqqyjyxk.dao.YwRqQyjyxkDao;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwPrintRqRqssylb;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxk;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalCyryzkdjb;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalGdyjgqjgdjb;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalPzrqgyzdjb;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalQysbclmc;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqjszdjb;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqjszrypbqk;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqjszzysb;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqqxdylb;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqqxsbylb;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqqxssdjb;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqssdjb;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqssylb;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRypbqk;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalZyaqjsglryzk;
import com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService;
import com.augurit.gzcgwsp.rqqyjyxk.web.form.PersonForm;
import com.augurit.gzcgwsp.rqqyjyxk.web.form.PzrqgyzdjbForm;
import com.augurit.gzcgwsp.rqqyjyxk.web.form.RqssdjbForm;
import com.augurit.gzcgwsp.rqqyjyxk.web.form.YwRqQyjyxkForm;
import com.augurit.gzcgwsp.rqqyjyxk.web.form.YwRqQyjyxkfbForm;
import com.augurit.gzcgwsp.sendNotice.service.IExchangeStateService;
import com.augurit.gzcgwsp.sendNotice.service.IYwMtbjlshInfoService;
import com.augurit.gzcgwsp.sendNotice.service.IYwWebserviceForWbService;
import com.augurit.gzcgwsp.sendNotice.service.IYwWebserviceInfoService;
import com.augurit.gzcgwsp.ssylb.dao.YwRqRqssylbDao;
import com.augurit.gzcgwsp.ssylb.entity.YwRqRqssylb;
import com.augurit.gzcgwsp.ssylb.web.form.YwRqRqssylbForm;
import com.augurit.gzcgwsp.webservice.SFTPUtil;
import com.augurit.gzcgwsp.wfex.common.service.IWfServiceEX;
import com.augurit.gzcgwsp.ywcmn.entity.CertificateNo;
import com.augurit.gzcgwsp.ywcmn.entity.YwComFinalQyxx;
import com.augurit.gzcgwsp.ywcmn.entity.YwComQyxx;
import com.augurit.gzcgwsp.ywcmn.entity.YwComSjcllist;
import com.augurit.gzcgwsp.ywcmn.service.ICertificateNoService;
import com.augurit.gzcgwsp.ywcmn.service.IYwComQyxxFinalService;
import com.augurit.gzcgwsp.ywcmn.service.IYwComQyxxService;
import com.augurit.gzcgwsp.ywcmn.service.IYwComSjclService;
import com.augurit.gzcgwsp.ywcmn.service.IYwComSjcllistService;
import com.augurit.gzcgwsp.ywcmn.web.form.YwComQyxxForm;
import com.augurit.gzcgwsp.ywcmn.web.form.YwComSjclForm;
import com.augurit.gzcgwsp.ywcmn.web.form.YwComSjcllistForm;
import com.augurit.wf.bi.web.form.WfBusInstanceTaskForm;
import com.augurit.wf.common.service.impl.WfBusClientServiceImpl;

@Service
@Transactional
public class YwRqQyjyxkServiceImpl extends WfBusClientServiceImpl<YwRqQyjyxkForm>  implements IYwRqQyjyxkService{
    private static final Log log=LogFactory.getLog(YwRqQyjyxkServiceImpl.class);
	@Autowired
	private YwRqQyjyxkDao ywRqQyjyxkDao;
	@Autowired
	private  ICertificateNoService  certificateNoService;
	@Autowired
	private YwRqRqssylbDao ywRqRqssylbDao;
    @Autowired
    private IYwComQyxxService ywComQyxxService;
    @Autowired
    private IYwComSjclService sjclService;
    @Autowired
	private IYwComSjcllistService ywComSjcllistService;
    @Autowired
    private IYwComSjclService ywComSjclService;
    @Autowired
    private IYwComQyxxFinalService ywComQyxxFinalService;
    @Autowired
    private IYwRqQyjyxkImportservice ywRqQyjyxkImportservice;
    @Autowired
    private IYwRqQyjyxkGdyjgqjgdjbService ywRqQyjyxkGdyjgqjgdjbService; 
    @Autowired
    private IYwRqQyjyxkRqssylbService ywRqQyjyxkRqssylbService;
    @Autowired
    private IYwMtbjlshInfoService ywMtbjlshInfoService;
    @Autowired
	private IYwWebserviceInfoService ywWebserviceInfoService;
    @Autowired
    private IYwWebserviceForWbService ywWebserviceForWbService;
    @Autowired
    private IExchangeStateService exchangeStateService;
    @Autowired
    private IWfServiceEX wfServiceEX;
	/**
	 * 根据主键获取Form对象
	 */
	@Transactional(readOnly = true)
	public YwRqQyjyxkForm getJyxk(Long id,String type) {
		YwRqQyjyxk entity = ywRqQyjyxkDao.getEntity(id);
		YwRqQyjyxkForm form=YwRqQyjyxkConvertor.convertVoToForm(entity);
		YwComSjcllistForm sjclListForm=new YwComSjcllistForm();
	       YwComSjclForm fkForm = new YwComSjclForm();
	        fkForm.setFkid(form.getQyid());
	        fkForm.setAjid(id);
	       
	    YwComQyxxForm qyxxform = ywComQyxxService.get(form.getQyid());
	    if("gd".equals(type)){//管道企业
	        sjclListForm.setTplcode("rqjyxkz_gdqy");
	        fkForm.setTplcode("rqjyxkz_gdqy");
	    }
	    if("pz".equals(type)){//液化企业
	        sjclListForm.setTplcode("rqjyxkz_pzqy");
	        fkForm.setTplcode("rqjyxkz_pzqy");
	    }
        List<YwComSjcllistForm> sjclList = ywComSjcllistService.getList(sjclListForm);
        List<YwComSjclForm> sjclTpl = ywComSjclService.getList(fkForm);
        for (YwComSjcllistForm listForm : sjclList) {
            boolean hasExists = false;
            for (YwComSjclForm tplForm : sjclTpl) {
                if (tplForm.getTplid() != null && listForm.getId().longValue() == tplForm.getTplid().longValue()) {
                    if (StringUtils.isNotBlank(tplForm.getFilepath())) {
                        listForm.setState("1");
                        listForm.setHasfile("1");
                        listForm.setFilename(tplForm.getFilename());
                        listForm.setFilepath(tplForm.getFilepath());
                        listForm.setFiletype(tplForm.getFiletype());
                    } else {
                        listForm.setState("0");
                        listForm.setHasfile("0");
                    }
                    if (tplForm.getYjs() != null) {
                        listForm.setYjs(Long.valueOf(tplForm.getYjs()));
                    }
                    if (tplForm.getFyjs() != null) {
                        listForm.setFyjs(Long.valueOf(tplForm.getFyjs()));
                    }
                    listForm.setClid(tplForm.getId());
                    if ("1".equals(listForm.getSfdz())) {
                        listForm.setDisabled("1");
                    } else {
                        listForm.setDisabled("0");
                        if (tplForm.getSfdz() == null) {
                            listForm.setSfdz("0");
                        } else {
                            listForm.setSfdz(tplForm.getSfdz());
                        }
                    }
                    hasExists = true;
                }
            }
            if (!hasExists) {
                listForm.setState("0");
                listForm.setHasfile("0");
                if ("1".equals(listForm.getSfdz())) {
                    listForm.setDisabled("1");
                } else {
                    listForm.setDisabled("0");
                }
            }
        }
        form.setSerialNo2(form.getSerialNo());
        form.setPassword2(form.getPassword());
        form.setAcceptNo2(form.getAcceptNo());
        form.setCbyw2(form.getCbyw());
        //form.setLxr2(form.getYwlxr());
        form.setSjr2(form.getSjr());
        form.setSqdw2(form.getSqdw());
        form.setFzjg(entity.getFzjg());
        form.setSajg2("广州市城市管理委员会");
        form.setSajg(form.getSajg2());
        SimpleDateFormat format = new SimpleDateFormat("yyyy年MM月dd日");
        System.out.println(form.getYwlxr()+"--------"+form.getAcceptNo2()+"------------------------"+form.getSqrq()+"--------------------"+form.getSqrq2());
        
        //form.setSqrq2(format.format(form.getSqrq()));
        YwRqQyjyxkConvertor.convertQyxxFormToForm(qyxxform, form);
        
        if(sjclList.size()>0)
		{
				YwComSjcllistForm sjclForm01=sjclList.get(0);
				form.setTitleRow101(sjclForm01.getTitle());
				form.setTitleRow102(sjclForm01.getTitle());
				form.setFyjsRow101(sjclForm01.getFyjs());
				form.setFyjsRow102(sjclForm01.getFyjs());
				form.setFsRow101(sjclForm01.getYjs()+sjclForm01.getFyjs());
				form.setFsRow102(sjclForm01.getYjs()+sjclForm01.getFyjs());
				if("0".equals(sjclForm01.getSfdz()))
				{
					form.setSfdzRow101("√");
					form.setSfdzRow102("√");
				}else{
					form.setSfdzRow101("√");
					form.setSfdzRow102("√");
				}
				
				YwComSjcllistForm sjclForm02=sjclList.get(1);
				form.setTitleRow201(sjclForm02.getTitle());
				form.setTitleRow202(sjclForm02.getTitle());
				form.setFyjsRow201(sjclForm02.getFyjs());
				form.setFyjsRow202(sjclForm02.getFyjs());
				form.setFsRow201(sjclForm02.getYjs()+sjclForm02.getFyjs());
				form.setFsRow202(sjclForm02.getYjs()+sjclForm02.getFyjs());
				if("0".equals(sjclForm02.getSfdz()))
				{
					form.setSfdzRow201("√");
					form.setSfdzRow202("√");
				}else{
					form.setSfdzRow201("√");
					form.setSfdzRow202("√");
				}
				
				YwComSjcllistForm sjclForm03=sjclList.get(2);
				form.setTitleRow301(sjclForm03.getTitle());
				form.setTitleRow302(sjclForm03.getTitle());
				form.setFyjsRow301(sjclForm03.getFyjs());
				form.setFyjsRow302(sjclForm03.getFyjs());
				form.setFsRow301(sjclForm03.getYjs()+sjclForm03.getFyjs());
				form.setFsRow302(sjclForm03.getYjs()+sjclForm03.getFyjs());
				if("0".equals(sjclForm03.getSfdz()))
				{
					form.setSfdzRow301("√");
					form.setSfdzRow302("√");
				}else{
					form.setSfdzRow301("√");
					form.setSfdzRow302("√");
				}
				
				YwComSjcllistForm sjclForm04=sjclList.get(3);
				form.setTitleRow401(sjclForm04.getTitle());
				form.setTitleRow402(sjclForm04.getTitle());
				form.setFyjsRow401(sjclForm04.getFyjs());
				form.setFyjsRow402(sjclForm04.getFyjs());
				form.setFsRow401(sjclForm04.getYjs()+sjclForm04.getFyjs());
				form.setFsRow402(sjclForm04.getYjs()+sjclForm04.getFyjs());
				if("0".equals(sjclForm04.getSfdz()))
				{
					form.setSfdzRow401("√");
					form.setSfdzRow402("√");
				}else{
					form.setSfdzRow401("√");
					form.setSfdzRow402("√");
				}
				
				
				YwComSjcllistForm sjclForm05=sjclList.get(4);
				form.setTitleRow501(sjclForm05.getTitle());
				form.setTitleRow502(sjclForm05.getTitle());
				form.setFyjsRow501(sjclForm05.getFyjs());
				form.setFyjsRow502(sjclForm05.getFyjs());
				form.setFsRow501(sjclForm05.getYjs()+sjclForm05.getFyjs());
				form.setFsRow502(sjclForm05.getYjs()+sjclForm05.getFyjs());
				if("0".equals(sjclForm05.getSfdz()))
				{
					form.setSfdzRow501("√");
					form.setSfdzRow502("√");
				}else{
					form.setSfdzRow501("√");
					form.setSfdzRow502("√");
				}
				
				
				YwComSjcllistForm sjclForm06=sjclList.get(5);
				form.setTitleRow601(sjclForm06.getTitle());
				form.setTitleRow602(sjclForm06.getTitle());
				form.setFyjsRow601(sjclForm06.getFyjs());
				form.setFyjsRow602(sjclForm06.getFyjs());
				form.setFsRow601(sjclForm06.getYjs()+sjclForm06.getFyjs());
				form.setFsRow602(sjclForm06.getYjs()+sjclForm06.getFyjs());
				if("0".equals(sjclForm06.getSfdz()))
				{
					form.setSfdzRow601("√");
					form.setSfdzRow602("√");
				}else{
					form.setSfdzRow601("√");
					form.setSfdzRow602("√");
				}
				
				YwComSjcllistForm sjclForm07=sjclList.get(6);
				form.setTitleRow701(sjclForm07.getTitle());
				form.setTitleRow702(sjclForm07.getTitle());
				form.setFyjsRow701(sjclForm07.getFyjs());
				form.setFyjsRow702(sjclForm07.getFyjs());
				form.setFsRow701(sjclForm07.getYjs()+sjclForm07.getFyjs());
				form.setFsRow702(sjclForm07.getYjs()+sjclForm07.getFyjs());
				if("0".equals(sjclForm07.getSfdz()))
				{
					form.setSfdzRow701("√");
					form.setSfdzRow702("√");
				}else{
					form.setSfdzRow701("√");
					form.setSfdzRow702("√");
				}
				
				YwComSjcllistForm sjclForm08=sjclList.get(7);
				form.setTitleRow801(sjclForm08.getTitle());
				form.setTitleRow802(sjclForm08.getTitle());
				form.setFyjsRow801(sjclForm08.getFyjs());
				form.setFyjsRow802(sjclForm08.getFyjs());
				form.setFsRow801(sjclForm08.getYjs()+sjclForm08.getFyjs());
				form.setFsRow802(sjclForm08.getYjs()+sjclForm08.getFyjs());
				if("0".equals(sjclForm08.getSfdz()))
				{
					form.setSfdzRow801("√");
					form.setSfdzRow802("√");
				}else{
					form.setSfdzRow801("√");
					form.setSfdzRow802("√");
				}
				
				YwComSjcllistForm sjclForm09=sjclList.get(8);
				form.setTitleRow901(sjclForm09.getTitle());
				form.setTitleRow902(sjclForm09.getTitle());
				form.setFyjsRow901(sjclForm09.getFyjs());
				form.setFyjsRow902(sjclForm09.getFyjs());
				form.setFsRow901(sjclForm09.getYjs()+sjclForm09.getFyjs());
				form.setFsRow902(sjclForm09.getYjs()+sjclForm09.getFyjs());
				if("0".equals(sjclForm09.getSfdz()))
				{
					form.setSfdzRow901("√");
					form.setSfdzRow902("√");
				}else{
					form.setSfdzRow901("√");
					form.setSfdzRow902("√");
				}
				
				YwComSjcllistForm sjclForm10=sjclList.get(9);
				form.setTitleRow1001(sjclForm10.getTitle());
				form.setTitleRow1002(sjclForm10.getTitle());
				form.setFyjsRow1001(sjclForm10.getFyjs());
				form.setFyjsRow1002(sjclForm10.getFyjs());
				form.setFsRow1001(sjclForm10.getYjs()+sjclForm10.getFyjs());
				form.setFsRow1002(sjclForm10.getYjs()+sjclForm10.getFyjs());
				if("0".equals(sjclForm10.getSfdz()))
				{
					form.setSfdzRow1001("√");
					form.setSfdzRow1002("√");
				}else{
					form.setSfdzRow1001("√");
					form.setSfdzRow1002("√");
				}
				
				YwComSjcllistForm sjclForm11=sjclList.get(10);
				form.setTitleRow1101(sjclForm11.getTitle());
				form.setTitleRow1102(sjclForm11.getTitle());
				form.setFyjsRow1101(sjclForm11.getFyjs());
				form.setFyjsRow1102(sjclForm11.getFyjs());
				form.setFsRow1101(sjclForm11.getYjs()+sjclForm11.getFyjs());
				form.setFsRow1102(sjclForm11.getYjs()+sjclForm11.getFyjs());
				if("0".equals(sjclForm11.getSfdz()))
				{
					form.setSfdzRow1101("√");
					form.setSfdzRow1102("√");
				}else{
					form.setSfdzRow1101("√");
					form.setSfdzRow1102("√");
				}
				
				YwComSjcllistForm sjclForm12=sjclList.get(11);
				form.setTitleRow1201(sjclForm12.getTitle());
				form.setTitleRow1202(sjclForm12.getTitle());
				form.setFyjsRow1201(sjclForm12.getFyjs());
				form.setFyjsRow1202(sjclForm12.getFyjs());
				form.setFsRow1201(sjclForm12.getYjs()+sjclForm12.getFyjs());
				form.setFsRow1202(sjclForm12.getYjs()+sjclForm12.getFyjs());
				if("0".equals(sjclForm12.getSfdz()))
				{
					form.setSfdzRow1201("√");
					form.setSfdzRow1202("√");
				}else{
					form.setSfdzRow1201("√");
					form.setSfdzRow1202("√");
				}
				
				YwComSjcllistForm sjclForm13=sjclList.get(12);
				form.setTitleRow1301(sjclForm13.getTitle());
				form.setTitleRow1302(sjclForm13.getTitle());
				form.setFyjsRow1301(sjclForm13.getFyjs());
				form.setFyjsRow1302(sjclForm13.getFyjs());
				form.setFsRow1301(sjclForm13.getYjs()+sjclForm13.getFyjs());
				form.setFsRow1302(sjclForm13.getYjs()+sjclForm13.getFyjs());
				if("0".equals(sjclForm13.getSfdz()))
				{
					form.setSfdzRow1301("√");
					form.setSfdzRow1302("√");
				}else{
					form.setSfdzRow1301("√");
					form.setSfdzRow1302("√");
				}
				
				YwComSjcllistForm sjclForm14=sjclList.get(13);
				form.setTitleRow1401(sjclForm14.getTitle());
				form.setTitleRow1402(sjclForm14.getTitle());
				form.setFyjsRow1401(sjclForm14.getFyjs());
				form.setFyjsRow1402(sjclForm14.getFyjs());
				form.setFsRow1401(sjclForm14.getYjs()+sjclForm14.getFyjs());
				form.setFsRow1402(sjclForm14.getYjs()+sjclForm14.getFyjs());
				if("0".equals(sjclForm14.getSfdz()))
				{
					form.setSfdzRow1401("√");
					form.setSfdzRow1402("√");
				}else{
					form.setSfdzRow1401("√");
					form.setSfdzRow1402("√");
				}
				
				YwComSjcllistForm sjclForm15=sjclList.get(14);
				form.setTitleRow1501(sjclForm15.getTitle());
				form.setTitleRow1502(sjclForm15.getTitle());
				form.setFyjsRow1501(sjclForm15.getFyjs());
				form.setFyjsRow1502(sjclForm15.getFyjs());
				form.setFsRow1501(sjclForm15.getYjs()+sjclForm15.getFyjs());
				form.setFsRow1502(sjclForm15.getYjs()+sjclForm15.getFyjs());
				if("0".equals(sjclForm15.getSfdz()))
				{
					form.setSfdzRow1501("√");
					form.setSfdzRow1502("√");
				}else{
					form.setSfdzRow1501("√");
					form.setSfdzRow1502("√");
				}
				
				YwComSjcllistForm sjclForm16=sjclList.get(15);
				form.setTitleRow1601(sjclForm16.getTitle());
				form.setTitleRow1602(sjclForm16.getTitle());
				form.setFyjsRow1601(sjclForm16.getFyjs());
				form.setFyjsRow1602(sjclForm16.getFyjs());
				form.setFsRow1601(sjclForm16.getYjs()+sjclForm16.getFyjs());
				form.setFsRow1602(sjclForm16.getYjs()+sjclForm16.getFyjs());
				if("0".equals(sjclForm16.getSfdz()))
				{
					form.setSfdzRow1601("√");
					form.setSfdzRow1602("√");
				}else{
					form.setSfdzRow1601("√");
					form.setSfdzRow1602("√");
				}
				
				YwComSjcllistForm sjclForm17=sjclList.get(16);
				form.setTitleRow1701(sjclForm17.getTitle());
				form.setTitleRow1702(sjclForm17.getTitle());
				form.setFyjsRow1701(sjclForm17.getFyjs());
				form.setFyjsRow1702(sjclForm17.getFyjs());
				form.setFsRow1701(sjclForm17.getYjs()+sjclForm17.getFyjs());
				form.setFsRow1702(sjclForm17.getYjs()+sjclForm17.getFyjs());
				if("0".equals(sjclForm17.getSfdz()))
				{
					form.setSfdzRow1701("√");
					form.setSfdzRow1702("√");
				}else{
					form.setSfdzRow1701("√");
					form.setSfdzRow1702("√");
				}
				YwComSjcllistForm sjclForm18=sjclList.get(17);
				form.setTitleRow1801(sjclForm18.getTitle());
				form.setTitleRow1802(sjclForm18.getTitle());
				form.setFyjsRow1801(sjclForm18.getFyjs());
				form.setFyjsRow1802(sjclForm18.getFyjs());
				form.setFsRow1801(sjclForm18.getYjs()+sjclForm17.getFyjs());
				form.setFsRow1802(sjclForm18.getYjs()+sjclForm17.getFyjs());
				if("0".equals(sjclForm18.getSfdz()))
				{
					form.setSfdzRow1801("√");
					form.setSfdzRow1802("√");
				}else{
					form.setSfdzRow1801("√");
					form.setSfdzRow1802("√");
				}
				
				if(sjclList!=null && sjclList.size()>18){
					YwComSjcllistForm sjclForm19=sjclList.get(18);
					form.setTitleRow1901(sjclForm19.getTitle());
					form.setTitleRow1902(sjclForm19.getTitle());
					form.setFyjsRow1901(sjclForm19.getFyjs());
					form.setFyjsRow1902(sjclForm19.getFyjs());
					form.setFsRow1901(sjclForm19.getYjs()+sjclForm17.getFyjs());
					form.setFsRow1902(sjclForm19.getYjs()+sjclForm17.getFyjs());
					if("0".equals(sjclForm19.getSfdz()))
					{
						form.setSfdzRow1901("√");
						form.setSfdzRow1902("√");
					}else{
						form.setSfdzRow1901("√");
						form.setSfdzRow1902("√");
					}
				
				}
		}
        form.setLxr2(form.getYwlxr());
        if(form.getSqrq() != null)
        	form.setSqrq2(format.format(form.getSqrq()));
        //System.out.println(form.getAcceptNo2()+"------------------------"+form.getSqrq()+"--------------------"+form.getSqrq2());
		return form;
	}
	
	public void changeYslState(Long id,Long state,String yslThyy) {
    	StringBuffer sql = new StringBuffer();
    	sql.append("update YwRqQyjyxk t set t.ysl="+state);
    	if(StringUtils.isNotBlank(yslThyy)){
    		sql.append(" ,t.yslThyy='"+yslThyy+"'");
    	}
    	sql.append(" where t.id="+id);
    	ywRqQyjyxkDao.getSession().createQuery(sql.toString()).executeUpdate();
    	System.out.println("经营许可收件");
    }
	 
	//是否延期办理
	    public void changeIsycbl(Long id, String state) {
//	    	YwRqQyjyxkForm form = this.get(id);
//	    	form.setIsycbl(state);
//	    	this.save(form);	
	    	
	    	YwRqQyjyxk entity = ywRqQyjyxkDao.get(id);
			entity.setIsycbl(state);
			ywRqQyjyxkDao.save(entity);	  
		}
		
	    //保存结案时间
		public void endSave(Long id) {
//			YwRqQyjyxkForm form = this.get(id);
//			form.setJarq(new Date());
//			this.save(form);
			
			YwRqQyjyxk entity = ywRqQyjyxkDao.get(id);
			entity.setJarq(new Date());
			ywRqQyjyxkDao.save(entity);
		}
	
	/**
	 * 删除Form对象列表
	 */
	public void delete(Long... ids) {
		ywRqQyjyxkDao.delete(ids);
	}

	/**
	 * 保存新增或修改的Form对象列表
	 */
	public void save(YwRqQyjyxkForm... forms) {
		if(forms != null)
			for(YwRqQyjyxkForm form : forms)
				this.save(form);
	}
	
	/**
	 * 保存新增或修改的Form对象
	 */
	public void save(YwRqQyjyxkForm form){
		if (form == null) return;
		
		YwRqQyjyxk entity = null;
		if(form != null && form.getId() != null){
			entity = ywRqQyjyxkDao.get(form.getId());
			//修改每次提交新增申请号码，比如表单提交的受理号是gz26120320XXXXX，但另外的acceptNo2是120327000636
        	if(StringUtils.isNotBlank(form.getAcceptNo()) && form.getAcceptNo().indexOf("XXXX")>0){
        		if(StringUtils.isNotBlank(form.getAcceptNo2()) && form.getAcceptNo2().indexOf("XXXX")<0){
        			form.setAcceptNo(form.getAcceptNo2());
        		}
        	} 
			if (StringUtils.isBlank(entity.getAcceptNo()) || form.getAcceptNo().indexOf("XXXX") > 0){ 
					form.setAcceptNo(null);
			}
		}else{
			entity = new YwRqQyjyxk();
            form.setAcceptNo(null);
		}
        
		//保存企业信息
        YwComQyxxForm qyxxForm = new YwComQyxxForm();
        if(form.getQyid() == null){
        	if(StringUtils.isNotBlank(form.getSqdw())){
            	YwComQyxxForm ywComQyxxForm = ywComQyxxService.getFormBySqdw(form.getSqdw(), form.getTemplateCode());
            	if(ywComQyxxForm != null){
            		qyxxForm = ywComQyxxForm;
            	}
            }
        }
        YwRqQyjyxkConvertor.convertFormToQyxxForm(form, qyxxForm);
        qyxxForm.setTemplateCode(form.getTemplateCode());
        ywComQyxxService.update(qyxxForm);        
        form.setQyid(qyxxForm.getId());
        
        boolean isNewForm = false;
        if (StringUtils.isBlank(form.getAcceptNo()) && StringUtils.isBlank(form.getAcceptNo2())) {

        	//生成政务中心统一查询编号 开始
			Map<String,String> serialNoAndPasswordMap = null;
        	if("Y".equals(form.getSendNotice())){//网办，1
        		serialNoAndPasswordMap = ywMtbjlshInfoService.getSerialNoAndPassword("1");
        	}else{//大厅，0
        		serialNoAndPasswordMap = ywMtbjlshInfoService.getSerialNoAndPassword("0");
        	}
        	String serialNo = serialNoAndPasswordMap.get("serialNo");
        	String password = serialNoAndPasswordMap.get("password");
			form.setSerialNo(serialNo);
			form.setPassword(password);
			form.setAcceptNo(serialNo);
    		//生成政务中心统一查询编号 结束
            isNewForm = true;
        
        }
        String acceptNo="";
        if(entity!=null && StringUtils.isNotBlank(entity.getAcceptNo())){//有时会出现acceptno2和acceptno不一致
        	 acceptNo=entity.getAcceptNo();
        	 form.setAcceptNo(acceptNo);
        }
        YwRqQyjyxkConvertor.convertFormToVo(form, entity);
        ywRqQyjyxkDao.save(entity);
		form.setId(entity.getId());
		form.setBranch(form.getTemplateCode());
		if (isNewForm) {
		    sjclService.updateByNewForm(form.getId());
		}
	}

	/**
	 * 根据Form对象的查询条件作分页查询
	 */
	@Transactional(readOnly = true)
	public Page<YwRqQyjyxkForm> search(Page<YwRqQyjyxkForm> page, YwRqQyjyxkForm form) {
		// 建立新的分页对象
		Page pg = PageUtils.newPageInstance(page);
		
		// 查询语句及参数
		StringBuffer hql = new StringBuffer("from YwRqQyjyxk ps where 1=1");
		List values = new ArrayList();
		
		// 查询条件
		if(form != null){
			
		}
		
		//排序
		hql.append(HqlUtils.buildOrderBy(page, "ps"));
		
		// 执行分页查询操作
		ywRqQyjyxkDao.findPage(pg, hql.toString(), values);
		
		// 转换为Form对象列表并赋值到原分页对象中
		List<YwRqQyjyxkForm> list = YwRqQyjyxkConvertor.convertVoListToFormList(pg.getResult());
		PageUtils.copy(pg, list, page);
		return page;
	}
	
	/**
	 * 根据受理号查询
	 */
	public List<YwRqQyjyxk> getByAcceptNo(String acceptNo) {
    	String sql = "from YwRqQyjyxk t where t.acceptNo='" + acceptNo + "'";
        List<YwRqQyjyxk> entityList = ywRqQyjyxkDao.find(sql);
        return entityList;
    }
	
	/**
	 * 根据过滤条件列表作分页查询
	 */
	@Transactional(readOnly = true)
	public Page<YwRqQyjyxkForm> search(Page<YwRqQyjyxkForm> page, List<PropertyFilter> filters) {
		// 建立新的分页对象
		Page<YwRqQyjyxk> pg = PageUtils.newPageInstance(page);
		
		// 按过滤条件分页查找对象
		ywRqQyjyxkDao.findPage(pg, filters);
		
		// 转换为Form对象列表并赋值到原分页对象中
		List<YwRqQyjyxkForm> list = YwRqQyjyxkConvertor.convertVoListToFormList(pg.getResult());
		PageUtils.copy(pg, list, page);
		return page;
	}

	public YwRqQyjyxkfbForm saveYwRqQyjyxkfb(String fbPath, YwRqQyjyxkfbForm ywRqQyjyxkfbForm) {
		try {
			File fbFile = new File(fbPath);
			if(fbFile.exists()){
				
				WritableCellFormat wcf = new WritableCellFormat(new WritableFont(WritableFont.createFont("宋体"),11));  
				wcf.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN); 
				wcf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
				wcf.setLocked(false);
				
				Workbook wb = Workbook.getWorkbook(fbFile);
				WritableWorkbook wwb = Workbook.createWorkbook(fbFile, wb);
				WritableSheet sheet = wwb.getSheet(0); 
				
				sheet.addCell(new Label(2,1, ywRqQyjyxkfbForm.getQymc(), wcf));
				sheet.addCell(new Label(2,2, ywRqQyjyxkfbForm.getZcdz(), wcf));
				sheet.addCell(new Label(2,3, ywRqQyjyxkfbForm.getZczb(), wcf));
				sheet.addCell(new Label(2,4, ywRqQyjyxkfbForm.getGqqy(), wcf));
				sheet.addCell(new Label(2,5, ywRqQyjyxkfbForm.getXkzbh(), wcf));
				sheet.addCell(new Label(2,6, ywRqQyjyxkfbForm.getXkzyxq(), wcf));
				sheet.addCell(new Label(2,7, ywRqQyjyxkfbForm.getYyzzzch(), wcf));
				
				List<PersonForm> pList = ywRqQyjyxkfbForm.getPersionList();
				for(int i=8; i<=11; i++){
					PersonForm pf = pList.get(i-8);
					sheet.addCell(new Label(2,i, pf.getMj(), wcf));
					sheet.addCell(new Label(4,i, pf.getZw(), wcf));
					sheet.addCell(new Label(6,i, pf.getZc(), wcf));
				}
				sheet.addCell(new Label(0,13, ywRqQyjyxkfbForm.getJyfw(), wcf));
				sheet.addCell(new Label(5,14, ywRqQyjyxkfbForm.getFzjg(), wcf));
				sheet.addCell(new Label(5,15, ywRqQyjyxkfbForm.getFzrq(), wcf));
				List<RqssdjbForm> rsList1 = ywRqQyjyxkfbForm.getRqssdjbList1();
				List<RqssdjbForm> rsList2 = ywRqQyjyxkfbForm.getRqssdjbList2();
				for(int i=19; i<=36; i++){
					RqssdjbForm rf = rsList1.get(i-19);
					sheet.addCell(new Label(0,i, rf.getXh(), wcf));
					sheet.addCell(new Label(1,i, rf.getMc(), wcf));
					sheet.addCell(new Label(3,i, rf.getDz(), wcf));
					sheet.addCell(new Label(5,i, rf.getSslb(), wcf));
				}
				for(int i=40; i<=57; i++){
					RqssdjbForm rf = rsList2.get(i-40);
					sheet.addCell(new Label(0,i, rf.getXh(), wcf));
					sheet.addCell(new Label(1,i, rf.getMc(), wcf));
					sheet.addCell(new Label(3,i, rf.getDz(), wcf));
					sheet.addCell(new Label(5,i, rf.getSslb(), wcf));
				}
				List<PzrqgyzdjbForm> pzrgList1 = ywRqQyjyxkfbForm.getPzrqgyzdjbList1();
				List<PzrqgyzdjbForm> pzrgList2 = ywRqQyjyxkfbForm.getPzrqgyzdjbList2();
				List<PzrqgyzdjbForm> pzrgList3 = ywRqQyjyxkfbForm.getPzrqgyzdjbList3();
				
				for(int i=61; i<=78; i++){
					PzrqgyzdjbForm rf = pzrgList1.get(i-61);
					sheet.addCell(new Label(0,i, rf.getXh(), wcf));
					sheet.addCell(new Label(1,i, rf.getMc(), wcf));
					sheet.addCell(new Label(3,i, rf.getDz(), wcf));
					sheet.addCell(new Label(5,i, rf.getGyzdj(), wcf));
				}
				for(int i=82; i<=99; i++){
					PzrqgyzdjbForm rf = pzrgList2.get(i-82);
					sheet.addCell(new Label(0,i, rf.getXh(), wcf));
					sheet.addCell(new Label(1,i, rf.getMc(), wcf));
					sheet.addCell(new Label(3,i, rf.getDz(), wcf));
					sheet.addCell(new Label(5,i, rf.getGyzdj(), wcf));
				}
				for(int i=103; i<=120; i++){
					PzrqgyzdjbForm rf = pzrgList3.get(i-103);
					sheet.addCell(new Label(0,i, rf.getXh(), wcf));
					sheet.addCell(new Label(1,i, rf.getMc(), wcf));
					sheet.addCell(new Label(3,i, rf.getDz(), wcf));
					sheet.addCell(new Label(5,i, rf.getGyzdj(), wcf));
				}
				wwb.write();
				wwb.close(); 
				wb.close();
			}
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
		return ywRqQyjyxkfbForm;
	}
	
	
	/**
	 * 根据附件获取副本的值 如果附件不存在就取数据库导入的基本信息的值，从项目试运行开始
	 * 都是没有副本的附件
	 */
	public YwRqQyjyxkfbForm getYwRqQyjyxkfb(String conextpath,String realpath,Long id) {
		YwRqQyjyxkfbForm yrf = new YwRqQyjyxkfbForm();
		try {
			if(StringUtils.isNotBlank(realpath)){
				String fbpath=conextpath+realpath;
				File fbFile = new File(fbpath);
				if(fbFile.exists()){
					Workbook workbook = Workbook.getWorkbook(fbFile);
					Sheet sheet = workbook.getSheet(0); 
					int rows=sheet.getRows();
					yrf.setQymc(sheet.getCell(2,1).getContents());
					yrf.setZcdz(sheet.getCell(2,2).getContents());
					yrf.setZczb(sheet.getCell(2,3).getContents());
					yrf.setGqqy(sheet.getCell(2,4).getContents());
					yrf.setXkzbh(sheet.getCell(2,5).getContents());
					yrf.setXkzyxq(sheet.getCell(2,6).getContents());
					yrf.setYyzzzch(sheet.getCell(2,7).getContents());	
					List<PersonForm> pList = new ArrayList<PersonForm>();
					for(int i=8; i<=11; i++){
						PersonForm pf = new PersonForm();
						pf.setMj(sheet.getCell(2, i).getContents());
						pf.setZw(sheet.getCell(4, i).getContents());
						pf.setZc(sheet.getCell(6, i).getContents());
						pList.add(pf);
					}
					yrf.setPersionList(pList);
					yrf.setJyfw(sheet.getCell(0, 13).getContents());
					yrf.setFzjg(sheet.getCell(5,14).getContents());
					yrf.setFzrq(sheet.getCell(5,15).getContents());
					List<RqssdjbForm> rsList1 = new ArrayList<RqssdjbForm>();
					List<RqssdjbForm> rsList2 = new ArrayList<RqssdjbForm>();
					for(int i=19; i<=36; i++){
						RqssdjbForm rf = new RqssdjbForm();
						rf.setXh(sheet.getCell(0, i).getContents());
						rf.setMc(sheet.getCell(1, i).getContents());
						rf.setDz(sheet.getCell(3, i).getContents());
						rf.setSslb(sheet.getCell(5, i).getContents());
						rsList1.add(rf);
					}
					for(int i=40; i<=57; i++){
						RqssdjbForm rf = new RqssdjbForm();
						rf.setXh(sheet.getCell(0, i).getContents());
						rf.setMc(sheet.getCell(1, i).getContents());
						rf.setDz(sheet.getCell(3, i).getContents());
						rf.setSslb(sheet.getCell(5, i).getContents());
						rsList2.add(rf);
					}
					yrf.setRqssdjbList1(rsList1);
					yrf.setRqssdjbList2(rsList2);
					List<PzrqgyzdjbForm> pzrgList1 = new ArrayList<PzrqgyzdjbForm>();
					List<PzrqgyzdjbForm> pzrgList2 = new ArrayList<PzrqgyzdjbForm>();
					List<PzrqgyzdjbForm> pzrgList3 = new ArrayList<PzrqgyzdjbForm>();
					for(int i=61; i<=78; i++){
						PzrqgyzdjbForm rf = new PzrqgyzdjbForm();
						rf.setXh(sheet.getCell(0, i).getContents());
						rf.setMc(sheet.getCell(1, i).getContents());
						rf.setDz(sheet.getCell(3, i).getContents());
						rf.setGyzdj(sheet.getCell(5, i).getContents());
						pzrgList1.add(rf);
					}
					for(int i=82; i<=99; i++){
						PzrqgyzdjbForm rf = new PzrqgyzdjbForm();
						rf.setXh(sheet.getCell(0, i).getContents());
						rf.setMc(sheet.getCell(1, i).getContents());
						rf.setDz(sheet.getCell(3, i).getContents());
						rf.setGyzdj(sheet.getCell(5, i).getContents());
						pzrgList2.add(rf);
					}
					for(int i=103; i<rows; i++){
						PzrqgyzdjbForm rf = new PzrqgyzdjbForm();
						rf.setXh(sheet.getCell(0, i).getContents());
						rf.setMc(sheet.getCell(1, i).getContents());
						rf.setDz(sheet.getCell(3, i).getContents());
						rf.setGyzdj(sheet.getCell(5, i).getContents());
						pzrgList3.add(rf);
					}
					yrf.setPzrqgyzdjbList1(pzrgList1);
					yrf.setPzrqgyzdjbList2(pzrgList2);
					yrf.setPzrqgyzdjbList3(pzrgList3);
					workbook.close(); 
			}else{
				//所有副本的数据从数据库查询
				yrf=buildYwRqQyjyxkfbFormById(id);
			}
		   }else{
			  //所有副本的数据从数据库查询
			   yrf= buildYwRqQyjyxkfbFormById(id);//不存在附件取值
		   }
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} 
		//加入有效期
		if(StringUtils.isBlank(yrf.getXkzyxq())){
			yrf.setXkzyxq(this.getJyxkValiddate(id));
		}
		
		
		return yrf;
	}
	
	
	
	
	
	/**
	 * 根据上传的基本信息构造一个 燃气经营许可证副本form对象
	 * @param id 
	 * @return
	 */
	private YwRqQyjyxkfbForm buildYwRqQyjyxkfbFormById(Long id) {	
		YwRqQyjyxk jyxk=ywRqQyjyxkDao.getYwRqQyjyxk(id,"id");//.getEntity(id);//查询实体主要是取出企业id
		YwRqQyjyxkfbForm entity=new YwRqQyjyxkfbForm();
		if(jyxk!=null){
			//基本信息转化为form
			entity.setQymc(jyxk.getSqdw());
			entity.setZcdz(jyxk.getZcdz());
			entity.setZczb(jyxk.getQyzczb());
			entity.setGqqy(jyxk.getGqqy());
			entity.setXkzbh(jyxk.getZsbh());
			entity.setFbzsbh(jyxk.getFbzsbh());//副本证书编号
			entity.setJyfw(jyxk.getJyfw());
			entity.setXkzyxq(jyxk.getXkzyxq()==null?"五年":jyxk.getXkzyxq());
			entity.setYyzzzch(jyxk.getGYyzzzch());
			entity.setFzrq(jyxk.getFbfzrq());
			entity.setFzjg(jyxk.getFzjg());
			entity.setZczb(entity.getZczb());
			//构建代表人
			buildPersonForm(entity,jyxk);
			//查询出燃气设施一览表的记录
			List<YwRqRqssylb> list=this.ywRqQyjyxkDao.getRqssylbs(jyxk.getId());
			buildRqss(entity,list);
			return entity;
		}
		else
			return entity;
		}

	/**
	 * 构造代表人如安全人等
	 * @param entity
	 * @param jyxk
	 */
	private void buildPersonForm(YwRqQyjyxkfbForm entity, YwRqQyjyxk jyxk) {
		List<PersonForm> pList = new ArrayList<PersonForm>();
		PersonForm frdb = new PersonForm();//法人代表
		frdb.setMj(jyxk.getFddbr());
		frdb.setZw(jyxk.getFddbrZw());
		frdb.setZc(jyxk.getFddbrZc());
		pList.add(frdb);
		PersonForm zyfzr = new PersonForm();//主要负责人
		zyfzr.setMj(jyxk.getZyfzr());
		zyfzr.setZw(jyxk.getZyfzrZw());
		zyfzr.setZc(jyxk.getZyfzrZc());
		pList.add(zyfzr);
		PersonForm aqfzr = new PersonForm();//安全负责人
		aqfzr.setMj(jyxk.getAqfzr());
		aqfzr.setZw(jyxk.getAqfzrZw());
		aqfzr.setZc(jyxk.getAqfzrZc());
		pList.add(aqfzr);
		PersonForm jsfzr = new PersonForm();//技术负责人
		jsfzr.setMj(jyxk.getJsfzr());
		jsfzr.setZw(jyxk.getJsfzrZw());
		jsfzr.setZc(jyxk.getJsfzrZc());
		pList.add(jsfzr);
		entity.setPersionList(pList);
	}

	/**
	 * 构造瓶装燃气设施
	 * @param entity 
	 * @param rpzqgys
	 */
	private void builPzRqgyz(YwRqQyjyxkfbForm entity,
			List<YwRqRqssylb> list) {
		List<PzrqgyzdjbForm> pzrgList1 = new ArrayList<PzrqgyzdjbForm>();
		List<PzrqgyzdjbForm> pzrgList2 = new ArrayList<PzrqgyzdjbForm>();
		List<PzrqgyzdjbForm> pzrgList3 = new ArrayList<PzrqgyzdjbForm>();
		//先构造空值
		for(int num1=1;num1<=18;num1++){
			PzrqgyzdjbForm form=new PzrqgyzdjbForm();
			pzrgList1.add(form);
		}
		for(int num2=1;num2<=18;num2++){
			PzrqgyzdjbForm form=new PzrqgyzdjbForm();
			pzrgList2.add(form);
		}
		for(int num3=1;num3<=18;num3++){
			PzrqgyzdjbForm form=new PzrqgyzdjbForm();
			pzrgList3.add(form);
		}
		int xh=1;//定义序号
		if(list!=null && list.size()>18){//如果燃气设置不止18条记录则分页
			if(list.size()>36){//分3页
				for(int k=0;k<18;k++){
					for(int i=0;i<=17;i++){
						YwRqRqssylb pzrq=list.get(k);
						PzrqgyzdjbForm rf =pzrgList1.get(k);
						rf.setXh(String.valueOf(xh++));
						rf.setMc(pzrq.getSsmc());
						rf.setDz(pzrq.getSsaddress());
						rf.setGyzdj(this.getGradeBySsdj(pzrq));
						rf.setId(pzrq.getSsylbid());
					}
				}
				for(int j=18;j<36;j++){
					YwRqRqssylb pzrq=list.get(j);
					PzrqgyzdjbForm rf =pzrgList2.get(j-pzrgList2.size());
					rf.setXh(String.valueOf(xh++));
					rf.setMc(pzrq.getSsmc());
					rf.setDz(pzrq.getSsaddress());
					rf.setGyzdj(this.getGradeBySsdj(pzrq));
					rf.setId(pzrq.getSsylbid());
				}
				for(int n=36;n<list.size();n++){
					YwRqRqssylb pzrq=list.get(n);
					PzrqgyzdjbForm rf =pzrgList3.get(n-(pzrgList1.size()+pzrgList2.size()));;
					rf.setXh(String.valueOf(xh++));
					rf.setMc(pzrq.getSsmc());
					rf.setDz(pzrq.getSsaddress());
					rf.setGyzdj(this.getGradeBySsdj(pzrq));
					rf.setId(pzrq.getSsylbid());
				}
			}else{//分2页
				for(int k=0;k<18;k++){
					YwRqRqssylb pzrq=list.get(k);
					PzrqgyzdjbForm rf =pzrgList1.get(k);
					rf.setXh(String.valueOf(xh++));
					rf.setMc(pzrq.getSsmc());
					rf.setDz(pzrq.getSsaddress());
					rf.setGyzdj(this.getGradeBySsdj(pzrq));
					rf.setId(pzrq.getSsylbid());
					
				}
				for(int j=18;j<list.size();j++){
					YwRqRqssylb pzrq=list.get(j);
					PzrqgyzdjbForm rf =pzrgList2.get(j-pzrgList2.size());;
					rf.setXh(String.valueOf(xh++));
					rf.setMc(pzrq.getSsmc());
					rf.setDz(pzrq.getSsaddress());
					rf.setGyzdj(this.getGradeBySsdj(pzrq));
					rf.setId(pzrq.getSsylbid());
				
				}
			}
		}else{
			if(list!=null && list.size()>0){
				for(int i=0;i<list.size();i++){
					YwRqRqssylb pzrq=list.get(i);
					PzrqgyzdjbForm rf = pzrgList1.get(i);
					rf.setXh(String.valueOf(xh++));
					rf.setMc(pzrq.getSsmc());
					rf.setDz(pzrq.getSsaddress());
					rf.setGyzdj(this.getGradeBySsdj(pzrq));
					rf.setId(pzrq.getSsylbid());
				}
			}
		}
		entity.setPzrqgyzdjbList1(pzrgList1);
		entity.setPzrqgyzdjbList2(pzrgList2);
		entity.setPzrqgyzdjbList3(pzrgList3);
	}

	/**
	 * 根据燃气设施登记表获取供应站的等级
	 * @return
	 */
	private String getGradeBySsdj(YwRqRqssylb ssylb){
		String sskh=ssylb.getSskh();//考核登记
		if("1".equals(sskh)){
			sskh="I级";
		}else if("2".equals(sskh)){
			sskh="II级";
		}else if("3".equals(sskh)){
			sskh="III级";
		}else{
			sskh=ssylb.getZdlb();
		}
		return sskh;
	}
	
	
	/**
	 * 构造燃气设施
	 * @param entity
	 * @param list
	 */
	private void buildRqss(YwRqQyjyxkfbForm entity,List<YwRqRqssylb> list){
		List<RqssdjbForm> rsList1 = new ArrayList<RqssdjbForm>();//燃气设施登记
		List<RqssdjbForm> rsList2 = new ArrayList<RqssdjbForm>();//燃气设施登记
		for(int num1=1;num1<=18;num1++){
			RqssdjbForm form=new RqssdjbForm();
			rsList1.add(form);
		}
		for(int num2=1;num2<=18;num2++){
			RqssdjbForm form=new RqssdjbForm();
			rsList2.add(form);
		}
		int xh=1;//燃气设施序号
		if(list!=null && list.size()>18){//如果燃气设置不止18条记录则分页
			for(int i=0;i<18;i++){//第一个集合
				YwRqRqssylb ssyl=list.get(i);
				RqssdjbForm rf = rsList1.get(i);
				rf.setXh(String.valueOf(xh++));
				rf.setMc(ssyl.getSsmc());
				rf.setDz(ssyl.getSsaddress());
				rf.setSslb(ssyl.getSslb());
				rf.setId(ssyl.getSsylbid());
				rf.setPagenum(ssyl.getPagenum());
				rf.setRownum(ssyl.getRowsnum());
			}
              for(int k=18;k<list.size();k++){//第二个集合
                    YwRqRqssylb ssyl=list.get(k);
                    RqssdjbForm rf = rsList2.get(k-rsList2.size());
                    rf.setXh(String.valueOf(xh++));
                    rf.setMc(ssyl.getSsmc());
                    rf.setDz(ssyl.getSsaddress());
                    rf.setSslb(ssyl.getSslb());
                    rf.setId(ssyl.getSsylbid());
                    rf.setPagenum(ssyl.getPagenum());
    				rf.setRownum(ssyl.getRowsnum());
                }
			

		}else{//小于或者等于18
			if(list!=null && list.size()>0){
				for(int i=0;i<list.size();i++){
					YwRqRqssylb ssyl=list.get(i);
					RqssdjbForm rf = rsList1.get(i);
					rf.setXh(String.valueOf(xh++));
					rf.setMc(ssyl.getSsmc());
					rf.setDz(ssyl.getSsaddress());
					rf.setSslb(ssyl.getSslb());
					rf.setId(ssyl.getSsylbid());
					rf.setPagenum(ssyl.getPagenum());
					rf.setRownum(ssyl.getRowsnum());
				}
			}
		}
		entity.setRqssdjbList1(rsList1);
		entity.setRqssdjbList2(rsList2);
	}
	
	

	/**
	 * 保存设施一览表
	 */
	public void saveSsylbs(List<YwRqRqssylb> ssylbs) {
		if(ssylbs!=null &&ssylbs.size()>0)
		{
			YwRqRqssylb ssylb;
			for(int i=0;i<ssylbs.size();i++)
			{
				ssylb=ssylbs.get(i);
				ywRqRqssylbDao.save(ssylb);
			}
		}
	}
	//所有设施一览表
	public List<YwRqRqssylb> findSsylbs(Long jyqyid) {
		List<YwRqRqssylb> list=ywRqRqssylbDao.createQuery("from YwRqRqssylb ssyl where ssyl.qyjyxkid='"+jyqyid+"' order by ssyl.id asc").list();
		return list;
	}
	
	//所有合格设施一览表
	public List<YwRqRqssylb> findPassSsylbs(Long jyqyid) {
		List<YwRqRqssylb> list=ywRqRqssylbDao.createQuery("from YwRqRqssylb ssyl where ssyl.qyjyxkid='"+jyqyid+"' and zdlb='合格' order by ssyl.id asc").list();
		return list;
	}
	//保存设施一览表
	public void saveSsylb(YwRqQyjyxkForm form,String ssylbid) throws Exception{
	    YwRqRqssylb ssylb=null;
	    if(StringUtils.isNotBlank(ssylbid)){
	        Long ssylbId=Long.valueOf(ssylbid);
	        ssylb=ywRqRqssylbDao.get(ssylbId);
	    }
	    if(ssylb==null){
	        ssylb=new YwRqRqssylb();
	    }
		ssylb.setSsmc(form.getSsmc());
		ssylb.setSslb(form.getSslb());
		ssylb.setZdlb(form.getZdlb());
		ssylb.setFzr(form.getFzr());
		ssylb.setSsaddress(form.getSsaddress());
		ssylb.setQyjyxkid(form.getId());
		ssylb.setFilepath(form.getFilepath());
		ssylb.setBszsbh(form.getBszsbh());
		ywRqRqssylbDao.save(ssylb);
		//保存到数据库
	}
/**
 * 删除设施一览表
 */
	public void delSsylb(Long id) {
		if(ywRqRqssylbDao.get(id)!=null)
		ywRqRqssylbDao.delete(ywRqRqssylbDao.get(id));
	}
	public void delSsylbs(Long id) {
		ywRqRqssylbDao.deleteAll(id);
	}
	/**
	 * 根据Id查找
	 */
	public YwRqRqssylb getSsylb(Long id) {
		return ywRqRqssylbDao.findById(id);
		}
	/**
     * 根据Id查找设施一览表
     */
    public YwRqQyjyxkForm getSsylbForm(YwRqQyjyxkForm form) {
        YwRqRqssylb entity = ywRqRqssylbDao.findById(form.getSsylbid());
        if(entity == null) return form;
        form.setSsmc(entity.getSsmc());
        form.setSsaddress(entity.getSsaddress());
        form.setSslb(entity.getSslb());
        form.setZdlb(entity.getZdlb());
        form.setFilepath(entity.getFilepath());
        form.setFzr(entity.getFzr());
        form.setSsylbid(entity.getSsylbid());
        form.setQyjyxkid(entity.getQyjyxkid());
        form.setBszsbh(entity.getBszsbh());
        return form;
    }
//	Excel到数据库的转换
	public List<YwRqRqssylb> convertExcelToDB(Long id) {
		String filepath=ywRqRqssylbDao.findFilePath(id);
		Workbook workbook =null;
		List<YwRqRqssylb> list=new ArrayList<YwRqRqssylb>();
		try {
			File fbFile = new File("/usr/local/tomcat/webapps/gzcgwsp"+filepath);
			System.out.println(fbFile+"---");
			if(fbFile.exists()){
				workbook = Workbook.getWorkbook(fbFile);
				Sheet sheet = workbook.getSheet(0); 
				for(int i=19; i<=36; i++){
					YwRqRqssylb ssylb = new YwRqRqssylb();
					//ssylb.setSsmc(sheet.getCell(0, i).getContents());
					ssylb.setSsmc(sheet.getCell(1, i).getContents());
					ssylb.setSsaddress(sheet.getCell(3, i).getContents());
					ssylb.setSslb(sheet.getCell(5, i).getContents());
					ssylb.setQyjyxkid(id);
					System.out.println(sheet.getCell(1, i).getContents()+"---");
					list.add(ssylb);
				}
				for(int i=40; i<=57; i++){
					YwRqRqssylb ssylb = new YwRqRqssylb();
					ssylb.setSsmc(sheet.getCell(1, i).getContents());
					ssylb.setSsaddress(sheet.getCell(3, i).getContents());
					ssylb.setSslb(sheet.getCell(5, i).getContents());
					ssylb.setQyjyxkid(id);
					System.out.println(sheet.getCell(1, i).getContents()+"---");
					list.add(ssylb);
				}
			}
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			//workbook.close(); 
		}
		return list;
	}
	
	
	/**
	 * 通过ID号,改变窗口是否可打印受理通知书状态(1为是；0和空为否)
	 * @param id id号
	 */
	public void changeSendAcceptNotice(Long id){
		YwRqQyjyxk entity = ywRqQyjyxkDao.get(id);
		entity.setSendAcceptNotice("1");
		ywRqQyjyxkDao.save(entity);
	}
	
	/**
	 * 通过ID号,改变窗口是否可打印延期办理结果通知书状态(1为是；0和空为否)
	 * @param id 
	 * @return 
	 */
	public void changeSendYqNotice(Long id){
		YwRqQyjyxk entity = ywRqQyjyxkDao.get(id);
		entity.setSendYqNotice("1");
		ywRqQyjyxkDao.save(entity);
	}
	
	/**
	 * 根据ID获得对象实体
	 * @param id
	 * @return
	 */  
	public YwRqQyjyxk getEntity(Long id){
		return ywRqQyjyxkDao.get(id);
	}

	/**
     * 保存对象实体
     * @param ywRqQyjyxk
     */     
    public void saveEntity(YwRqQyjyxk ywRqQyjyxk){
    	ywRqQyjyxkDao.save(ywRqQyjyxk);
    }

    /*
     * 生成证书编号
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#createZsbh(com.augurit.gzcgwsp.rqqyjyxk.web.form.YwRqQyjyxkForm)
     */
    public void createZsbh(YwRqQyjyxkForm form,int type,String ssylbid,boolean isPrint) {
        String num="01";
        String prefix="";
        Long maxnum=certificateNoService.getMaxNum(type);
        maxnum=maxnum+1;
        if(maxnum>99){//最大只能是99
            maxnum=Long.valueOf(99);
        }
        if(type==1){//
            int length=4-String.valueOf(maxnum).length();
            for(int i=0;i<length;i++){
                prefix+="0";
            }
            num+=prefix+"-"+maxnum;
            form.setZsbh(num);//经营许可证编号
           
        }else if(type==2){//设施编号
            YwRqQyjyxk jyxk=ywRqQyjyxkDao.getEntity(form.getId());
            Long sslybnum=getNumSsxk(form.getId(),Long.valueOf(ssylbid));
            String jyxkbh="";
            if(jyxk!=null){
                jyxkbh=jyxk.getZsbh()==null?"":jyxk.getZsbh();
                if(StringUtils.isNotBlank(jyxkbh)){
                    jyxkbh=form.getZsbh();
                }else{
                	String sql=" from YwRqQyjyxk t where t.zsbh is not null and rownum=1  and t.qyid="+form.getQyid()+" order by id desc";
                	List<YwRqQyjyxk> list=ywRqQyjyxkDao.find(sql);
                	if(list != null && list.size() > 0){
                		YwRqQyjyxk entity=list.get(0);
                    	jyxkbh=entity.getZsbh();
                	}
                }
            }
            if(StringUtils.isNotBlank(jyxkbh)){//取经营许可的中间证号
                jyxkbh=jyxkbh.substring(3,jyxkbh.length());
            }
            int length=3-String.valueOf(sslybnum).length();
            for(int i=0;i<length;i++){
                prefix+="0";
            }
            num="01-"+jyxkbh+"-"+prefix+sslybnum;
            YwRqRqssylb sslyb=ywRqRqssylbDao.get(Long.valueOf(ssylbid));
            if(sslyb!=null){
                form.setFzr(sslyb.getFzr());//设置负责人
            }
            if(sslyb!=null){//点击打印的时候才再一次生成号码,并要是发证日期是空的情况
            	if(StringUtils.isBlank(sslyb.getBszsbh())){
	            	int maxsslyb=this.maxNumSslyb(form.getId());
	            	 int len=3-String.valueOf(maxsslyb+1).length();
	            	 String prefixstr="";
	                 for(int i=0;i<len;i++){
	                	 prefixstr+="0";
	                 }
	            	String printnum="01-"+jyxkbh+"-"+prefixstr+(maxsslyb+1);
	            	 sslyb.setBszsbh(printnum);
	            	 if(StringUtils.isBlank(sslyb.getValiddate())){
	            		 sslyb.setValiddate(this.getJyxkValiddate(form.getId())); 
	            	 }
	            	 if(sslyb.getFzdate()==null){
	  	            		sslyb.setFzdate(DateUtilsEX.dateFormate(new Date(), "yyyy-MM-dd"));	
	  	            	 }
            		 form.setBszsbh(printnum);//供应设施许可证编号
            	}
            }
        }
    }

    /**
     * 获取经营许可的有效日期
     * @return
     */
    public String getJyxkValiddate(Long jyxkId){
    	String sql=" from YwRqQyjyxk where qyid in (select qyid from YwRqQyjyxk t where t.id="+jyxkId+")" +
    			" and endday is not null and endyear is not null and endmonth is not null order by id asc";
    	List<YwRqQyjyxk> list=this.ywRqQyjyxkDao.find(sql);
    	try {
			if(list!=null){
				YwRqQyjyxk entity=list.get(0);
				String endyear=DateUtilsEX.convertNumByStr(entity.getEndyear());
				String endmonth=DateUtilsEX.convertNumByStr(entity.getEndmonth());
				String endday=DateUtilsEX.ChineseDay2Number(entity.getEndday());
			    if(StringUtils.isNotBlank(endmonth) && Integer.valueOf(endmonth)<10){
			    	endmonth="0"+endmonth;
			    }
			    if(StringUtils.isNotBlank(endday) && Integer.valueOf(endday)<10){
			    	endday="0"+endday;
			    }
				return endyear+"-"+endmonth+"-"+endday;
			}
		} catch (Exception e) {
			log.error("转换出错："+e.getMessage()); 
			e.printStackTrace();
		}
    	return null;
    }
    
    /**
     * 获取前面一共发了多少个证书
     * @param jyxkId
     * @return
     */
    private int maxNumSslyb(Long jyxkId){
    	 String hql = "select count(ssylbid) from "+YwRqRqssylb.class.getName()+" where qyjyxkid in(" +
    	 		" select t.id from YwRqQyjyxk t where t.qyid=(select  qyid from YwRqQyjyxk where id="+jyxkId+"))" +
    	 				" and zdlb='合格' and fzdate is not null";
    	 List<Object> list=this.ywRqQyjyxkDao.find(hql);
    	 if(list.size()>0){
    		 return ((Long)list.get(0)).intValue();
    	 }
    	 return 0;
    }
    
    
    /**
     * 查询出燃气设施是企业的第几家设施
     * @param qyid
     * @return
     */
    private Long getNumSsxk(Long ajid,Long ssid){
        Long num=0l;
        String hql = "select rownum,ssylbid from "+YwRqRqssylb.class.getName()+" where qyjyxkid='"+ajid+"'";
        List<Object[]> list=this.ywRqQyjyxkDao.find(hql);
        if(ssid!=null){
            if(list!=null && list.size()>0){
                for(Object[] objs:list){
                    Long idobj=(Long) objs[1];
                    if(String.valueOf(idobj).equals(String.valueOf(ssid))){
                        num=(Long) objs[0];
                    }
                }
            }
        }
        return num;
    }
    
    
    /*
     * 打印时候保存许可证书
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#saveQyjyxk(com.augurit.gzcgwsp.rqqyjyxk.web.form.YwRqQyjyxkForm)
     */
    public void saveQyjyxk(YwRqQyjyxkForm form,int type) {
        //createZsbh(form,type);
        if(type==1){//经营许可证
            if(StringUtils.isBlank(form.getZsbh())){
                createZsbh(form,type,null,false);
            }
        }else if(type==2){//设施许可证
            if(StringUtils.isBlank(form.getBszsbh())){
                createZsbh(form,type,null,false);
            }
        }
        isSaveZsBh(form.getId(),type);
        save(form);
        
    }
    
    /**
     * 是否保存证书编号
     * @param ajid 案件id
     */
    private void isSaveZsBh(Long  ajid,int type){
      //设置并保存编号
        boolean flag=certificateNoService.getNo(ajid,1);
        if(!flag){//保存证书编号
            CertificateNo certificateNo=new CertificateNo();
            certificateNo.setAjid(ajid);
            certificateNo.setZstype(type);
            certificateNoService.save(certificateNo);
        }
    }

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#getYwComFinalQyxx(java.lang.Long)
     */
    public YwComFinalQyxx getYwComFinalQyxx(Long qyid) {
        return ywComQyxxFinalService.getEntityByQyId(qyid);
    }

    

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#importFinalData(java.util.Map)
     */
    public String importFinalData(YwRqQyjyxkForm form,Map map) {
        String result="";
        String templateCode=(String) map.get("templateCode");
        String ajid=(String) map.get("id");
        String contextpath=(String) map.get("contextPath");
        Long tempId=getTempId(templateCode);
        //查询上传的材料
        List<YwComSjclForm> sjclTpl = getFiles(form.getQyid(), Long.valueOf(ajid), templateCode);
        String filepath=this.getFilePath(sjclTpl,tempId,contextpath);
        if(StringUtils.isNotBlank(filepath)){
            filepath=contextpath+filepath;
            map.put("filepath", filepath);
            result+=ywRqQyjyxkImportservice.importFinalData(form,map);
        }else{
            result+="收件材料没有符合条件的文件\n";
            
        }
        return result;
    }
    
    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#checkData(java.lang.String, java.lang.Long)
     */
    public boolean checkData(String templateCode, Long ajid,Long qyid) {
        Long tempId=getTempId(templateCode);
        //查询上传的材料
        //List<YwComSjcl>  sjcls=this.ywComSjclService.getFormByAjId(Long.valueOf(ajid),tempId);
        List<YwComSjclForm> sjclTpl = this.getFiles(qyid, ajid, templateCode);
        if(!hasFile(tempId,sjclTpl)){
            return false;
        }else{
            return true;
        }
    }
    
    private List<YwComSjclForm> getFiles(Long qyid,Long ajid,String templateCode){
        YwComSjclForm fkForm = new YwComSjclForm();
        fkForm.setFkid(qyid);
        fkForm.setAjid(ajid);
        fkForm.setTplcode(templateCode);
        List<YwComSjclForm> sjclTpl = ywComSjclService.getList(fkForm);
        return sjclTpl;
    }
    
    /**
     * 获取申请表文件
     * @param sjcls
     * @return
     */
    private String getFilePath( List<YwComSjclForm> sjcls,Long tempId,String contextpath){
        String filepath="";
        Workbook wb;
        try {
            for(YwComSjclForm entity:sjcls){
                if(String.valueOf(entity.getTplid()).equals(String.valueOf(tempId))){
                    filepath=entity.getFilepath();
                    if(StringUtils.isNotBlank(filepath)){
                        String[] paths=filepath.split(",");
                    for(String path:paths){
                            path=path.replace("/", File.separator);
                            File file=new File(contextpath+path);
                            String name=entity.getFilename();
                            if(StringUtils.isNotBlank(name)){
                                for(String filename:name.split(",")){
                                filename=filename.substring(filename.lastIndexOf(".")+1,filename.length());
                                if(!"xls".equals(filename)){
                                    continue;
                                }
                                if(!file.exists()){
                                    continue;
                                }else{
                                    wb = Workbook.getWorkbook(file);
                                    if(isCorrectFile(wb)){
                                        return path;
                                    }
                               }
                              }
                            }
                       
                        }
                    }
                }
            }
        } catch (Exception e) {
           e.printStackTrace();
        }
        return filepath;
    }
    
    /**
     * 是否是正确的文件格式
     * @param wb
     * @return
     */
    private boolean isCorrectFile(Workbook wb){
        Sheet[] sheets=wb.getSheets();
        if(sheets.length==9)
            return true;
        else
            return false;
    }

            
    /**
     * 返回模版id
     * @param templateCode 模版材料名称
     * @return
     */
    private Long getTempId(String templateCode){
        //查询材料模版
        List<YwComSjcllist> sjcllists = ywComSjcllistService.getYwComSjcls(templateCode);
        Long tempId=0l;
        for(YwComSjcllist entity:sjcllists){
            String title=entity.getTitle();
            if(title.indexOf("申请表")>=0){
                tempId=entity.getId();
                break;
            }
        }
        return tempId;
    }
    
    /**
     * 是否存在文件
     * @return
     */
    private boolean hasFile(Long tempId, List<YwComSjclForm> list){
        String filenames="";
        if(list!=null && list.size()>0){
            for(YwComSjclForm entity:list){
                    if(String.valueOf(entity.getTplid()).equals(String.valueOf(tempId))){
                        String filename=entity.getFilename();
                        if(StringUtils.isNotBlank(filename)){
                            filename=filename.substring(filename.lastIndexOf(".")+1,filename.length());
                            if("xls".equals(filename)){
                                filenames=filename;
                                break;
                            }
                        }
                    }
               }
            }
           if(StringUtils.isNotBlank(filenames))  return true;
        
        return false;
    }

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#getYwComQyxx(java.lang.Long, java.lang.String)
     */
    public YwComQyxx getYwComQyxx(Long qyid, String acceptNo) {
        YwComQyxx qyxx=ywComQyxxService.getYwComQyxx(qyid, acceptNo);
        return qyxx;
    }

   
    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#getSsylbs(java.lang.Long, java.lang.String)
     */
    public List<YwRqRqssylb> getSsylbs(Long qyjyxkid, String acceptNo) {
        String sql=" from "+YwRqRqssylb.class.getName()+"  where qyjyxkid='"+qyjyxkid+"'";
        List<YwRqRqssylb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#getFinalSsylbDatas(java.lang.Long)
     */
    public List<YwRqQyjyxkFinalRqssylb> getFinalSsylbDatas(Long qyid) {
        String sql=" from "+YwRqQyjyxkFinalRqssylb.class.getName()+"  where qyid='"+qyid+"'";
        List<YwRqQyjyxkFinalRqssylb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#getGdyjs(java.lang.Long, java.lang.String)
     */
    public   List<YwRqQyjyxkGdyjgqjgdjb>  getGdyjs(Long qyid, String acceptNo) {
        String sql=" from "+YwRqQyjyxkGdyjgqjgdjb.class.getName()+"  where qyid='"+qyid+"' and acceptNo='"+acceptNo+"'";
        List<YwRqQyjyxkGdyjgqjgdjb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#getFinalGdyjs(java.lang.Long)
     */
    public List<YwRqQyjyxkFinalGdyjgqjgdjb> getFinalGdyjs(Long qyid) {
        String sql=" from "+YwRqQyjyxkFinalGdyjgqjgdjb.class.getName()+"  where qyid='"+qyid+"'";
        List<YwRqQyjyxkFinalGdyjgqjgdjb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }


    /*
     * 
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#saveGdInfos(com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkGdyjgqjgdjb)
     */
    public void saveGdInfos(YwRqQyjyxkGdyjgqjgdjb entity) {
       ywRqQyjyxkGdyjgqjgdjbService.save(entity);
    }

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#getGdyj(java.lang.Long)
     */
    public YwRqQyjyxkGdyjgqjgdjb getGdyj(Long gdId) {
        String sql=" from "+YwRqQyjyxkGdyjgqjgdjb.class.getName()+"  where id='"+gdId+"'";
        List<YwRqQyjyxkGdyjgqjgdjb>  list=ywRqQyjyxkDao.find(sql);
        if(list!=null && list.size()>0){
            return list.get(0);
        }
    
        return null;
    }

    public void deleteGdgq(Long gdid) {
        YwRqQyjyxkGdyjgqjgdjb entity= this.getGdyj(gdid);
        ywRqQyjyxkGdyjgqjgdjbService.delete(entity);
        
    }

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#getCyrys(java.lang.Long, java.lang.String)
     */
    public List<YwRqQyjyxkCyryzkdjb> getCyrys(Long qyid, String acceptNo) {
        String sql=" from "+YwRqQyjyxkCyryzkdjb.class.getName()+"  where qyid='"+qyid+"' and acceptNo='"+acceptNo+"'";
        List<YwRqQyjyxkCyryzkdjb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    /**
     * 最终数据从业人员
     */
    public List<YwRqQyjyxkFinalCyryzkdjb> getFinalCyrys(Long qyid) {
        String sql=" from "+YwRqQyjyxkFinalCyryzkdjb.class.getName()+"  where qyid='"+qyid+"'";
        List<YwRqQyjyxkFinalCyryzkdjb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#getFinalRqqxds(java.lang.Long)
     */
    public List<YwRqQyjyxkFinalRqqxdylb> getFinalRqqxds(Long qyid) {
        String sql=" from "+YwRqQyjyxkFinalRqqxdylb.class.getName()+"  where qyid='"+qyid+"'";
        List<YwRqQyjyxkFinalRqqxdylb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#getFinalRqqxsbs(java.lang.Long)
     */
    public List<YwRqQyjyxkFinalRqqxsbylb> getFinalRqqxsbs(Long qyid) {
        String sql=" from "+YwRqQyjyxkFinalRqqxsbylb.class.getName()+"  where qyid='"+qyid+"'";
        List<YwRqQyjyxkFinalRqqxsbylb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkFinalRqqxssdjb> getFinalRqqxss(Long qyid) {
        String sql=" from "+YwRqQyjyxkFinalRqqxssdjb.class.getName()+"  where qyid='"+qyid+"'";
        List<YwRqQyjyxkFinalRqqxssdjb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkFinalZyaqjsglryzk> getFinalZyaqjs(Long qyid) {
        String sql=" from "+YwRqQyjyxkFinalZyaqjsglryzk.class.getName()+"  where qyid='"+qyid+"'";
        List<YwRqQyjyxkFinalZyaqjsglryzk>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkRqqxdylb> getRqqxds(Long qyid, String acceptNo) {
        String sql=" from "+YwRqQyjyxkRqqxdylb.class.getName()+"  where qyid='"+qyid+"'";
        List<YwRqQyjyxkRqqxdylb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkRqqxsbylb> getRqqxsbs(Long qyid, String acceptNo) {
        String sql=" from "+YwRqQyjyxkRqqxsbylb.class.getName()+"  where qyid='"+qyid+"' and acceptNo='"+acceptNo+"'";
        List<YwRqQyjyxkRqqxsbylb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkRqqxssdjb> getRqqxss(Long qyid, String acceptNo) {
        String sql=" from "+YwRqQyjyxkRqqxssdjb.class.getName()+"  where qyid='"+qyid+"' and acceptNo='"+acceptNo+"'";
        List<YwRqQyjyxkRqqxssdjb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkZyaqjsglryzk> getZyaqjs(Long qyid, String acceptNo) {
        String sql=" from "+YwRqQyjyxkZyaqjsglryzk.class.getName()+"  where qyid='"+qyid+"' and acceptNo='"+acceptNo+"'";
        List<YwRqQyjyxkZyaqjsglryzk>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#getRqqxssGyz(java.lang.Long)
     */
    public List<YwRqQyjyxkFinalPzrqgyzdjb> getRqqxssGyz(Long qyid) {
        String sql=" from "+YwRqQyjyxkFinalPzrqgyzdjb.class.getName()+"  where qyid='"+qyid+"'";
        List<YwRqQyjyxkFinalPzrqgyzdjb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkPzrqgyzdjb> getRqqxssGyz(Long qyid, String acceptNo) {
        String sql=" from "+YwRqQyjyxkPzrqgyzdjb.class.getName()+"  where qyid='"+qyid+"' and acceptNo='"+acceptNo+"'";
        List<YwRqQyjyxkPzrqgyzdjb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }


    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#getRqqxssDjbs(java.lang.Long, java.lang.String)
     */
    public List<YwRqQyjyxkRqssdjb> getRqqxssDjbs(Long qyid, String acceptNo) {
        String sql=" from "+YwRqQyjyxkRqssdjb.class.getName()+"  where qyid='"+qyid+"' and acceptNo='"+acceptNo+"'";
        List<YwRqQyjyxkRqssdjb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#getRqqxssfinalDjbs(java.lang.Long)
     */
    public List<YwRqQyjyxkFinalRqssdjb> getRqqxssfinalDjbs(Long qyid) {
        String sql=" from "+YwRqQyjyxkFinalRqssdjb.class.getName()+"  where qyid='"+qyid+"'";
        List<YwRqQyjyxkFinalRqssdjb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkFinalQysbclmc> getFinalQysbclmc(Long qyid) {
        String sql=" from "+YwRqQyjyxkFinalQysbclmc.class.getName()+"  where qyid='"+qyid+"'";
        List<YwRqQyjyxkFinalQysbclmc>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkFinalRqjszdjb> getFinalRqjszdjb(Long qyid) {
        String sql=" from "+YwRqQyjyxkFinalRqjszdjb.class.getName()+"  where qyid='"+qyid+"'";
        List<YwRqQyjyxkFinalRqjszdjb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkQysbclmc> getQysbclmc(Long qyid, String acceptNo) {
        String sql=" from "+YwRqQyjyxkQysbclmc.class.getName()+"  where qyid='"+qyid+"' and acceptNo='"+acceptNo+"'";
        List<YwRqQyjyxkQysbclmc>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkRqjszdjb> getRqjszdjb(Long qyid) {
        String sql=" from "+YwRqQyjyxkRqjszdjb.class.getName()+"  where qyid='"+qyid+"'";
        List<YwRqQyjyxkRqjszdjb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkFinalRypbqk> getPzrybqks(Long qyid) {
        String sql=" from "+YwRqQyjyxkFinalRypbqk.class.getName()+"  where qyid='"+qyid+"'";
        List<YwRqQyjyxkFinalRypbqk>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkRypbqk> getPzrybqks(Long qyid, String acceptNo) {
        String sql=" from "+YwRqQyjyxkRypbqk.class.getName()+"  where qyid='"+qyid+"' and acceptNo='"+"'";
        List<YwRqQyjyxkRypbqk>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkFinalRqjszzysb> getfinalzysbs(Long qyid) {
        String sql=" from "+YwRqQyjyxkFinalRqjszzysb.class.getName()+"  where qyid='"+qyid+"'";
        List<YwRqQyjyxkFinalRqjszzysb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkRqjszzysb> getzysbs(Long qyid, String acceptNo) {
        String sql=" from "+YwRqQyjyxkRqjszzysb.class.getName()+"  where qyid='"+qyid+"' and acceptNo='"+acceptNo+"'";
        List<YwRqQyjyxkRqjszzysb>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkFinalRqjszrypbqk> getfinaljszrypbqks(Long qyid) {
        String sql=" from "+YwRqQyjyxkFinalRqjszrypbqk.class.getName()+"  where qyid='"+qyid+"'";
        List<YwRqQyjyxkFinalRqjszrypbqk>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    public List<YwRqQyjyxkRqjszrypbqk> getjszrypbqks(Long qyid,
            String acceptNo) {
        String sql=" from "+YwRqQyjyxkRqjszrypbqk.class.getName()+"  where qyid='"+qyid+"' and acceptNo='"+acceptNo+"'";
        List<YwRqQyjyxkRqjszrypbqk>  list=ywRqQyjyxkDao.find(sql);
        return list;
    }

    /*
     * 导入企业正式信息
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#importFinalQyData(java.util.Map)
     */
    public Map importFinalQyData(YwRqQyjyxkForm form,Map map) {
        this.ywRqQyjyxkImportservice.importFinalQyData(form,map);

        return null;
    }

    public YwRqQyjyxkForm get(Long id) {
        return this.getJyxk(id, "");
    }

    public void saveSsylb(String contextpath,YwRqRqssylbForm form) {
        YwRqRqssylb ssylb=null;
        if(form.getSsylbid()!=null){
            Long ssylbId=Long.valueOf(form.getSsylbid());
            ssylb=ywRqRqssylbDao.findById(ssylbId);
           // if(!form.getSskh().equals(ssylb.getSskh())){
                //删除考核表文件
                //String path=contextpath+ssylb.getFilepath();
               // File file=new File(path);
                //if(!file.exists()){
                    //file.delete();
                //}
            //}
        }
        if(ssylb==null){
            ssylb=new YwRqRqssylb();
        }
        ssylb.setSsmc(form.getSsmc());
        ssylb.setSslb(form.getSslb());
        ssylb.setZdlb(form.getZdlb());
        ssylb.setBszsbh(form.getBszsbh());
        ssylb.setFzr(form.getFzr());
        if(StringUtils.isNotBlank(form.getTotalVol())){
            ssylb.setTotalVol(form.getTotalVol());    
        }
        if(StringUtils.isNotBlank(form.getSingleVol())){
            ssylb.setSingleVol(form.getSingleVol());
        }
        
        if(StringUtils.isNotBlank(form.getFjPath())){
        	ssylb.setFjPath(form.getFjPath());
        }
        if(StringUtils.isNotBlank(form.getFjType())){
        	ssylb.setFjType(form.getFjType());
        }
        if(StringUtils.isNotBlank(form.getFjName())){
        	ssylb.setFjName(form.getFjName());
        }
        
        ssylb.setFzdate(form.getFzdate());
        ssylb.setFzorg(form.getFzorg());
        ssylb.setValiddate(form.getValiddate());
        ssylb.setSsaddress(form.getSsaddress());
        ssylb.setQyjyxkid(form.getQyjyxkid());
     
        YwRqQyjyxkForm rqjyxkForm=this.get(form.getQyjyxkid());
        String sqdw=rqjyxkForm.getSqdw();
        //新增或者修改自动生成文件
        if(ssylb.getSsylbid()==null || StringUtils.isBlank(ssylb.getFilepath())
        		|| !form.getSskh().equals(ssylb.getSskh()) || !form.getSslb().equals(ssylb.getSslb())){
            ssylb.setSskh(form.getSskh());
            String filepath=ywRqQyjyxkRqssylbService.createFilePathByContent(contextpath, sqdw,ssylb);
            if(StringUtils.isNotBlank(filepath)){
                ssylb.setFilepath(filepath);
            }
        }
       
        ywRqRqssylbDao.save(ssylb);
        
    }
    
    /**
	 * 保存设施一览表 附件
	 * @param fjOldName 附件原名
	 * @param fjType 附件类型
	 * @param uploadFile 流
	 * @throws Exception
	 */
	public String uploadSsylbFj(String targetFilePath,String fjOldName,String fjType, InputStream uploadFile)throws Exception{
		// 随机生成文件名后缀
		int bytesum = 0;
		int byteread = 0;
		String formatDate = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
		int random = new Random().nextInt(1000000);
		String name = formatDate+random+"."+fjType;
		String address = "/resources/AttacheFile/";
		FileOutputStream fs = new FileOutputStream(targetFilePath+name);
//		String sql = "update YwYnJzfqwczcJbxx jz set jz."+fjFlag+"='"+address+name+"', jz."+fjFlag+"name='"+fjOldName+"',jz."+fjFlag+"type='"+fjType+"' where jz.id="+ajid;
//		System.out.println("sql = "+sql);
		try{
//			Query query = ywYnJzfqwczcJbxxDao.getSession().createQuery(sql);
//			query.executeUpdate();
			byte[] buffer = new byte[1024];
			while ((byteread = uploadFile.read(buffer)) != -1) {
				bytesum += byteread; // 字节数 文件大小
				fs.write(buffer, 0, byteread);
			}
			fs.flush();
			fs.close();
			uploadFile.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		  //上传附件到另外一台服务器
    	SFTPUtil.uploadOtherFile(address, targetFilePath+name);  
		return address+name;
	}

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#saveJyxkFb(com.augurit.gzcgwsp.rqqyjyxk.web.form.YwRqQyjyxkfbForm, com.augurit.gzcgwsp.rqqyjyxk.web.form.YwRqQyjyxkForm, java.util.Map, java.util.Map)
     */
    public void saveJyxkFb(YwRqQyjyxkfbForm fb, YwRqQyjyxkForm form,
            Map<String, List<RqssdjbForm>> rqssMap,
            Map<String, List<PzrqgyzdjbForm>> pzMap) {
       //this.save(form);
       YwRqQyjyxk jyxk=ywRqQyjyxkDao.getYwRqQyjyxk(form.getId(),"id");//.getEntity(id);//查询实体主要是取出企业id
       if(jyxk!=null){
           this.saveJyxk(fb, form, jyxk);
           this.savaComQyxx(form,fb);
           //查询出燃气设施一览表的记录
           List<RqssdjbForm> list1=rqssMap.get("listRqssdjb1");
           List<RqssdjbForm> list2=rqssMap.get("listRqssdjb2");
           saveSslyb(list1,jyxk);
           saveSslyb(list2,jyxk);
           //瓶装设施
           List<PzrqgyzdjbForm> pzlist1=pzMap.get("listPzRqssdjb1");
           List<PzrqgyzdjbForm> pzlist2=pzMap.get("listPzRqssdjb2");
           List<PzrqgyzdjbForm> pzlist3=pzMap.get("listPzRqssdjb3");
           savePzSs(pzlist1,jyxk);
           savePzSs(pzlist2,jyxk);
           savePzSs(pzlist3,jyxk);
       }
    }
    
    /**
     * 打印时保存经营许可
     */
    private void saveJyxk(YwRqQyjyxkfbForm fb, YwRqQyjyxkForm form,YwRqQyjyxk jyxk){
        //基本信息保存
        jyxk.setSqdw(fb.getQymc());
        jyxk.setZcdz(fb.getZcdz());
        jyxk.setQyzczb(fb.getZczb());
        jyxk.setGqqy(fb.getGqqy());
        jyxk.setFbfzrq(fb.getFzrq());
        jyxk.setXkzyxq(fb.getXkzyxq());
        jyxk.setFbzsbh(fb.getFbzsbh());
        jyxk.setJyfw(fb.getJyfw());
        jyxk.setGYyzzzch(fb.getYyzzzch());
        jyxk.setJsfzr(form.getJsfzr());
        jyxk.setJsfzrZc(form.getJsfzrZc());
        jyxk.setJsfzrZw(form.getJsfzrZw());
        jyxk.setAqfzr(form.getAqfzr());
        jyxk.setAqfzrZc(form.getAqfzrZc());
        jyxk.setAqfzrZw(form.getAqfzrZw());
        jyxk.setZyfzr(form.getZyfzr());;
        jyxk.setZyfzrZc(form.getZyfzrZc());
        jyxk.setZyfzrZw(form.getZyfzrZw());
        jyxk.setFddbr(form.getFddbr());
        jyxk.setFddbrZc(form.getFddbrZc());
        jyxk.setFddbrZw(form.getFddbrZw());
        jyxk.setFzjg(fb.getFzjg());
        this.saveEntity(jyxk);
    }
    
    private void savaComQyxx(YwRqQyjyxkForm form,YwRqQyjyxkfbForm fb){
        YwComQyxx entity=this.getYwComQyxx(form.getQyid(), form.getAcceptNo());
        entity.setFddbr(form.getFddbr());
        entity.setFddbrZc(form.getFddbrZc());
        entity.setFddbrZw(form.getFddbrZw());
        entity.setJsfzr(form.getJsfzr());
        entity.setJsfzrZc(form.getJsfzrZc());
        entity.setJsfzrZw(form.getJsfzrZw());
        entity.setFddbr(form.getFddbr());
        entity.setFddbrZc(form.getFddbrZc());
        entity.setFddbrZw(form.getFddbrZw());
        entity.setZczj(fb.getZczb());
        this.ywComQyxxService.saveEntity(entity);
    }

    

/**
 * 保存燃气设施
 * @param list
 */
private void saveSslyb( List<RqssdjbForm> list,YwRqQyjyxk jyxk){
//    List<YwRqRqssylb> datas=ywRqRqssylbDao.findBy("qyjyxkid", jyxk.getId());
//    for(YwRqRqssylb ssylb:datas){
//        ywRqRqssylbDao.delete(ssylb);
//    }
    for(RqssdjbForm ssdj:list){
        Long id=ssdj.getId();
        if(id!=null){
        	YwRqRqssylb entity=ywRqRqssylbDao.get(id);
        	if(entity!=null){
        		 entity.setSsmc(ssdj.getMc());
                 entity.setSslb(ssdj.getSslb());
                 entity.setSsaddress(ssdj.getDz());
                 entity.setQyjyxkid(jyxk.getId());
                 entity.setPagenum(ssdj.getPagenum());
                 entity.setRowsnum(ssdj.getRownum());
                 entity.setZdlb("合格");
                 ywRqRqssylbDao.save(entity);
        	}else{//第一次保存
        		YwRqRqssylb ylb=new YwRqRqssylb();
                if(StringUtils.isNotBlank(ssdj.getMc())){
                	ylb.setSsmc(ssdj.getMc());
                	ylb.setSslb(ssdj.getSslb());
                	ylb.setSsaddress(ssdj.getDz());
                	ylb.setQyjyxkid(jyxk.getId());
                	 entity.setZdlb("合格");
                	ylb.setPagenum(ssdj.getPagenum());
                	ylb.setRowsnum(ssdj.getRownum());
                    ywRqRqssylbDao.save(ylb);
                }
        	}
           
        }else{
        	YwRqRqssylb ylb=new YwRqRqssylb();
            if(StringUtils.isNotBlank(ssdj.getMc())){
            	ylb.setSsmc(ssdj.getMc());
            	ylb.setSslb(ssdj.getSslb());
            	ylb.setSsaddress(ssdj.getDz());
            	ylb.setQyjyxkid(jyxk.getId());
            	ylb.setZdlb("合格");
            	ylb.setPagenum(ssdj.getPagenum());
            	ylb.setRowsnum(ssdj.getRownum());
                ywRqRqssylbDao.save(ylb);
            }
        }
    }
}

/**
 * 保存燃气登记站
 * @param list
 */
private void savePzSs( List<PzrqgyzdjbForm> list,YwRqQyjyxk jyxk){
//    List<YwRqQyjyxkPzrqgyzdjb> datas=ywRqQyjyxkPzrqgyzdjbService.findDataByJyxk(jyxk);
//    for(YwRqQyjyxkPzrqgyzdjb ssylb:datas){
//        ywRqQyjyxkPzrqgyzdjbService.deleteEntity(ssylb);
//    }
    for(PzrqgyzdjbForm ssdj:list){
        Long id=ssdj.getId();
        if(id!=null){
        	YwPrintRqRqssylb entity=ywRqRqssylbDao.getPrintEntity(id);
        	if(entity!=null){
        		entity.setSsmc(ssdj.getMc());
                entity.setSslb("供应站");
                entity.setSslbdj(ssdj.getGyzdj());
                entity.setZdlb(ssdj.getGyzdj());
                entity.setSsaddress(ssdj.getDz());
                entity.setQyjyxkid(jyxk.getId());
                ywRqRqssylbDao.savePrintEntity(entity);
        	}else{
        		YwPrintRqRqssylb ylb=new YwPrintRqRqssylb();
        		ylb.setSsmc(ssdj.getMc());
        		ylb.setSslb("供应站");
        		ylb.setZdlb(ssdj.getGyzdj());
        		ylb.setQyjyxkid(jyxk.getId());
        		ylb.setSsaddress(ssdj.getDz());
                ywRqRqssylbDao.savePrintEntity(ylb);
        	}
        	
        }else{
        	YwPrintRqRqssylb entity=new YwPrintRqRqssylb();
        	entity.setSsmc(ssdj.getMc());
            entity.setSslb("供应站");
            entity.setZdlb(ssdj.getGyzdj());
            entity.setSslbdj(ssdj.getGyzdj());
            entity.setSsaddress(ssdj.getDz());
            entity.setQyjyxkid(jyxk.getId());
            ywRqRqssylbDao.savePrintEntity(entity);
        }
    }
}

public Map<String, String> buildMap(YwRqQyjyxkForm form,String zstype) {
    Map<String, String> map=new HashMap<String,String>();
   if("jyxkz".equals(zstype)){//主要证书的map
       if(StringUtils.isNotBlank(form.getFzjg()))
           map.put("fzjg", form.getFzjg());
       else
           map.put("fzjg", "广州市城市管理委员会");
        map.put("endyear", form.getEndyear());
        map.put("endmonth", form.getEndmonth());
        map.put("endday", form.getEndday());
        map.put("fddbr", form.getFddbr());
        if(StringUtils.isNotBlank(form.getGqqy())){
            //判断字数
            String gqqy=form.getGqqy();
            if(gqqy.length()>25){
                String newvalue=gqqy.substring(0,25);
                String val=gqqy.substring(21,gqqy.length());
                gqqy=newvalue+"\n"+val;
            }
            map.put("gqqy",gqqy);
        }
        if(StringUtils.isNotBlank(form.getAddress())){
            //判断字数
            String address=form.getAddress();
            if(address.length()>25){
                String newvalue=address.substring(0,25);
                String val=address.substring(25,address.length());
                address=newvalue+"\r\n"+val;
                map.put("address", address);
            }
        }
        map.put("sqdw", form.getSqdw());
        map.put("zsbh", form.getZsbh());
        map.put("jyfw", form.getJyfw());
        map.put("startyear", form.getStartyear());
        map.put("startmonth", form.getStartmonth());
        map.put("startday", form.getStartday());
   }
    return map;
}



/**
 * 
 */
public Map<String, String> createMap(YwRqQyjyxkForm form, YwRqQyjyxkfbForm fb) {
   //基本信息
    Map<String, String> map=this.createBaseMap(form, fb);
    map=createSsylb(fb,map);//燃气设施一览表
    map=createPzgyz(fb,map);//燃气登记
    return map;
}
/**
 * 瓶装燃气供应站登记表
 * @param fb
 * @param map
 * @return
 */
private Map<String, String> createPzgyz(YwRqQyjyxkfbForm fb,
        Map<String, String> map) {
    List<PzrqgyzdjbForm> list1 =fb.getPzrqgyzdjbList1();
    List<PzrqgyzdjbForm> list2 =fb.getPzrqgyzdjbList2();
    List<PzrqgyzdjbForm> list3=fb.getPzrqgyzdjbList3();
    int index1=1;
    int index2=1;
    int index3=1;
    for(PzrqgyzdjbForm rqdjb :list1){
        if(StringUtils.isNotBlank(rqdjb.getMc()) && StringUtils.isNotBlank(rqdjb.getDz())){
            map.put("pzrqXh1", String.valueOf(index1++));
            map.put("pzrqMc1", rqdjb.getMc());
            map.put("pzrqDz1",rqdjb.getDz());
            map.put("pzrqGyzdj1",rqdjb.getGyzdj());
        }
    }
    for(PzrqgyzdjbForm rqdjb :list2){
        if(StringUtils.isNotBlank(rqdjb.getMc()) && StringUtils.isNotBlank(rqdjb.getDz())){
            map.put("pzrqXh2", String.valueOf(index2++));
            map.put("pzrqMc2", rqdjb.getMc());
            map.put("pzrqDz2",rqdjb.getDz());
            map.put("pzrqGyzdj2",rqdjb.getGyzdj());
        }
    }
    
    for(PzrqgyzdjbForm rqdjb :list3){
        if(StringUtils.isNotBlank(rqdjb.getMc()) && StringUtils.isNotBlank(rqdjb.getDz())){
            map.put("pzrqXh3", String.valueOf(index3++));
            map.put("pzrqMc3", rqdjb.getMc());
            map.put("pzrqDz3",rqdjb.getDz());
            map.put("pzrqGyzdj3",rqdjb.getGyzdj());
        }
    }
    return map;
}

/**
 * 燃气设施一览表
 * @param form
 * @param fb
 * @param map
 * @return
 */
private Map<String, String> createSsylb(
        YwRqQyjyxkfbForm fb, Map<String, String> map) {
    List<RqssdjbForm> rgssList1 =fb.getRqssdjbList1();
    List<RqssdjbForm> rgssList2 =fb.getRqssdjbList2();
    int index1=1;
    int index2=1;
    for(RqssdjbForm rqss :rgssList1){//燃气设施登记表
        if(StringUtils.isNotBlank(rqss.getMc()) && StringUtils.isNotBlank(rqss.getDz())){
            map.put("rqssXh1", String.valueOf(index1++));
            map.put("rqssMc1", rqss.getMc());
            map.put("rqssDz1",rqss.getDz());
            map.put("rqssSslb1",rqss.getSslb());
        }
    }
    
    for(RqssdjbForm rqss :rgssList2){//燃气设施登记表2
        if(StringUtils.isNotBlank(rqss.getMc()) && StringUtils.isNotBlank(rqss.getDz())){
            map.put("rqssXh2", String.valueOf(index2++));
            map.put("rqssMc2", rqss.getMc());
            map.put("rqssDz2",rqss.getDz());
            map.put("rqssSslb2",rqss.getSslb());
        }
    }
    return map;
}

public static  void main(String args[]) throws FileNotFoundException {
    String inpath="D:\\home\\MyEclipse 6.5\\workspace\\gzcgw\\WebApp\\Templates\\rqjyxk\\rqjyxkfb.xls";
   
    YwRqQyjyxkServiceImpl service=new YwRqQyjyxkServiceImpl();
    InputStream uploadFile=new FileInputStream(new File(inpath));
    Workbook wb=ExcelUtil.getWorkBookByInputStream(uploadFile);
    Sheet[] sheets=wb.getSheets();
    Calendar cal=Calendar.getInstance();
    int year=cal.get(Calendar.YEAR);
    int month=cal.get(Calendar.MONTH);
    int day=cal.get(Calendar.DATE);

}


/**
 * 开始写文件
 * @param inFilePath
 * @param outFilePath
 * @return
 */
private String writerDBDataToExcel(String inFilePath, String outFilePath,YwRqQyjyxkForm form, YwRqQyjyxkfbForm fb){
    WritableWorkbook wwb;
    try {
        wwb = ExcelUtil.createWorkbookByOld(inFilePath, outFilePath);
        WritableSheet wsOne = ExcelUtil.getWritableSheetByIndex(wwb, 0);
        WritableSheet wsTwo = ExcelUtil.getWritableSheetByIndex(wwb, 1);
        WritableSheet wsThree = ExcelUtil.getWritableSheetByIndex(wwb, 2);
        copyWsOne(wsOne,form,fb);
        copyWsTwo(wsTwo,form,fb);
        copyWsThree(wsThree,form,fb);
        wsOne.setProtected(true);
        wsTwo.setProtected(true);
        wsThree.setProtected(true);
        wwb.write();
        wwb.close();
    } catch (Exception e) {
        log.error("写文件错误"+e);
    } 
    
    return outFilePath;
}

/**
 * 写第一个工作区域表
 * @param wsOne
 * @param form
 * @param fb
 */
private void copyWsOne(WritableSheet wsOne, YwRqQyjyxkForm form,
        YwRqQyjyxkfbForm fb) {
        //mergerCells(wsOne);//先合并单元格
    try {
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"),12);
        WritableCellFormat wcf = new WritableCellFormat(font,NumberFormats.TEXT);
        wcf.setAlignment(Alignment.LEFT);
        wcf.setVerticalAlignment(VerticalAlignment.CENTRE); 
        wcf.setLocked(true);
        WritableCellFormat wcf1=ExcelUtil.createCellFormat("宋体", 10, Alignment.CENTRE, VerticalAlignment.CENTRE, false);
        WritableCellFormat wcf2=ExcelUtil.createCellFormat("宋体", 12, Alignment.LEFT, VerticalAlignment.TOP, false);
        WritableCellFormat datewcf=ExcelUtil.createCellFormat("宋体", 12, Alignment.CENTRE, VerticalAlignment.JUSTIFY, false);
        wcf1.setLocked(true);
        wcf2.setLocked(true);
        wcf1.setWrap(true);
        wcf2.setWrap(true);
        wcf.setWrap(true);
        datewcf.setLocked(true);
        ExcelUtil.modiStrCell2(wsOne, 1, 1, form.getSqdw()==null?"":form.getSqdw(), wcf);//单位名称
        if(StringUtils.isNotBlank(fb.getZcdz())){//如果副本的地址为空，则取form的地址
        	fb.setZcdz(form.getAddress());
        }
        String regMoney="";
        if(StringUtils.isNotBlank(form.getZczj())){
        	if(form.getZczj().indexOf("万")<=0){
        		regMoney=form.getZczj()+"万";
        	}else{
        		regMoney=form.getZczj();
        	}
        }
        if(StringUtils.isBlank(regMoney)){
        	if(fb.getZczb().indexOf("万")<=0){
        		regMoney=fb.getZczb()+"万";
        	}else{
        		regMoney=fb.getZczb();
        	}
        }
        
        changContentStyle(wsOne, 1, 2 ,form.getAddress(),20,25,9,10,12);//注册地址
        ExcelUtil.modiStrCell2(wsOne, 1, 3, regMoney, wcf);//注册资本
        changContentStyle(wsOne, 1, 5, fb.getGqqy(),20,25,9,10,12);//供气区域
        ExcelUtil.modiStrCell2(wsOne, 1, 7, form.getZsbh()==null?"":"粤建燃证字"+form.getZsbh()+"号", wcf);//许可证编号
        ExcelUtil.modiStrCell2(wsOne, 1, 9, fb.getXkzyxq()==null?"五年":fb.getXkzyxq(), wcf);//证书有效期
        ExcelUtil.modiStrCell2(wsOne, 1, 12, fb.getYyzzzch()==null?"":fb.getYyzzzch(), wcf);//营业执照注册号
        ExcelUtil.modiStrCell2(wsOne, 1, 14, form.getFddbr()==null?"":form.getFddbr(), wcf);//法定代表人
        ExcelUtil.modiStrCell2(wsOne, 3, 14, form.getFddbrZw()==null?"":form.getFddbrZw(),  wcf1);
        ExcelUtil.modiStrCell2(wsOne, 5, 14, form.getFddbrZc()==null?"":form.getFddbrZc(), wcf1);
        ExcelUtil.modiStrCell2(wsOne, 1, 16, form.getZyfzr()==null?"":form.getZyfzr(), wcf);//主要负责人
        ExcelUtil.modiStrCell2(wsOne, 3, 16, form.getZyfzrZw()==null?"":form.getZyfzrZw(),  wcf1);//
        ExcelUtil.modiStrCell2(wsOne, 5, 16, form.getZyfzrZc()==null?"":form.getZyfzrZc(), wcf1);//
        ExcelUtil.modiStrCell2(wsOne, 1, 18, form.getAqfzr()==null?"":form.getAqfzr(), wcf);//安全负责人
        ExcelUtil.modiStrCell2(wsOne, 3, 18, form.getAqfzrZw()==null?"":form.getAqfzrZw(),  wcf1);
        ExcelUtil.modiStrCell2(wsOne, 5, 18, form.getAqfzrZc()==null?"":form.getAqfzrZc(), wcf1);
        ExcelUtil.modiStrCell2(wsOne, 1, 20, form.getJsfzr()==null?"":form.getJsfzr(), wcf);//技术负责人
        ExcelUtil.modiStrCell2(wsOne, 3, 20, form.getJsfzrZw()==null?"":form.getJsfzrZw(),  wcf1);
        ExcelUtil.modiStrCell2(wsOne, 5, 20, form.getJsfzrZc()==null?"":form.getJsfzrZc(), wcf1);
        ExcelUtil.modiStrCell2(wsOne, 1, 24, form.getJyfw()==null?"":form.getJyfw(), wcf2);// 经营范围
        ExcelUtil.modiStrCell2(wsOne, 3, 32, fb.getFzjg()==null?"广州市城市管理委员会":fb.getFzjg(), wcf);//发证机关
        ExcelUtil.modiStrCell2(wsOne, 4, 33, fb.getFzrq()==null?DateUtilsEX.dateFormate(new Date(), "yyyy年MM月dd日"):fb.getFzrq(), datewcf);
        //开始写套打的第二页数据
        this.writeRqss(wsOne, form, fb);
       
    }catch (Exception e) {
        log.error("写入基本信息错误"+e.getMessage());
    }
    
}

	/**
	 * 查询燃气设施
	 * @param form
	 * @param isAll
	 * @return
	 */
	
 public List<RqssdjbForm> getRqssdjbForm(YwRqQyjyxkForm form,boolean isAll){
	String sql= "from YwRqRqssylb t where t.qyjyxkid="+form.getId()+" and t.sslb!='供应站' " +
	" and t.zdlb='合格'   order by t.ssylbid asc";
	if(isAll){
		sql= "from YwRqRqssylb t where t.qyjyxkid="+form.getId()+
		" and t.zdlb='合格'   order by t.ssylbid asc";
	}
	Query query = ywRqQyjyxkDao.getSession().createQuery(sql);
	//query.setFirstResult(0); 
	//query.setMaxResults(17);
	List<YwRqRqssylb> list=query.list();
    
    List<RqssdjbForm> newdatas=new ArrayList<RqssdjbForm>();
    for(YwRqRqssylb rqss:list){
        if(StringUtils.isNotBlank(rqss.getSsmc())){
        	RqssdjbForm rqssForm=new RqssdjbForm();
        	rqssForm.setMc(rqss.getSsmc());
        	rqssForm.setDz(rqss.getSsaddress());
        	rqssForm.setSslb(rqss.getSslb());
        	rqssForm.setPagenum(rqss.getPagenum());
        	rqssForm.setRownum(rqss.getRowsnum());
            newdatas.add(rqssForm);
        }
    }
    return newdatas;
}

private List<PzrqgyzdjbForm> getRqssdjbByGyz(YwRqQyjyxkForm form){
	String sql= "from YwRqRqssylb t where t.qyjyxkid="+form.getId()+" and t.sslb='供应站' " +
	" and t.zdlb='合格'   order by t.ssylbid asc";
	Query query = ywRqQyjyxkDao.getSession().createQuery(sql);
	//query.setFirstResult(0); 
	//query.setMaxResults(17);
	List<YwRqRqssylb> list=query.list();
    
    List<PzrqgyzdjbForm> newdatas=new ArrayList<PzrqgyzdjbForm>();
    for(YwRqRqssylb rqss:list){
        if(StringUtils.isNotBlank(rqss.getSsmc())){
        	PzrqgyzdjbForm rqssForm=new PzrqgyzdjbForm();
        	rqssForm.setMc(rqss.getSsmc());
        	rqssForm.setDz(rqss.getSsaddress());
        	rqssForm.setGyzdj(this.getGradeBySsdj(rqss));
            newdatas.add(rqssForm);
        }
    }
    return newdatas;
}

/**
 * 写第二页的数据
 * @param wsOne
 * @param form
 * @param fb
 */
private void copyWsTwo(WritableSheet wsTwo, YwRqQyjyxkForm form,
        YwRqQyjyxkfbForm fb) {
    try {
        List<RqssdjbForm> newdatas=getRqssdjbForm(form,false);
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"),10);
        WritableCellFormat wcf = new WritableCellFormat(font);
        wcf.setAlignment(Alignment.LEFT);
        wcf.setVerticalAlignment(VerticalAlignment.CENTRE); 
        wcf.setWrap(true);
        wcf.setLocked(true);
        if(newdatas!=null &&newdatas.size()>18){
            for(int i=18;i<newdatas.size();i++){
                RqssdjbForm rqss=newdatas.get(i);
                writeOneSqssRowData(wsTwo, 0, i+1, String.valueOf(1+i), rqss, wcf);
            }
        }
        
        //第四页燃气供应站数据写入
        List<PzrqgyzdjbForm> pzlist=this.getRqssdjbByGyz(form);
        List<PzrqgyzdjbForm> pznewdatas=new ArrayList<PzrqgyzdjbForm>();
        for(PzrqgyzdjbForm rqss:pzlist){
            if(StringUtils.isNotBlank(rqss.getMc())){
                pznewdatas.add(rqss);
            }
        }
        //写入前18条数据
        if(pznewdatas!=null &&pznewdatas.size()<18){
            for(int i=0;i<pznewdatas.size();i++){
                PzrqgyzdjbForm pzrqss=pznewdatas.get(i);
                this.writeOnePzRowData(wsTwo, 5, i+1,  String.valueOf(1+i), pzrqss, wcf);
            }
        }
        // 将其他的空白锁定
        if(pznewdatas.size()<18){
        	 for(int k=1+pznewdatas.size();k<=19;k++){
             	ExcelUtil.modiStrCell2(wsTwo, 0, k, "", wcf);//
             	ExcelUtil.modiStrCell2(wsTwo, 1, k, "", wcf);//
             	ExcelUtil.modiStrCell2(wsTwo, 2, k, "", wcf);//
             	ExcelUtil.modiStrCell2(wsTwo, 3, k, "", wcf);//
             }
        }
         //将其他的空白锁定
        if(pzlist.size()<18){
        	 for(int k=1+pzlist.size();k<=19;k++){
             	ExcelUtil.modiStrCell2(wsTwo, 0, k, "", wcf);//
             	ExcelUtil.modiStrCell2(wsTwo, 1, k, "", wcf);//
             	ExcelUtil.modiStrCell2(wsTwo, 2, k, "", wcf);//
             	ExcelUtil.modiStrCell2(wsTwo, 3, k, "", wcf);//
             }
        }
    } catch (Exception e) {
    	e.printStackTrace();
        log.error("写入第二页出现错误"+e.getMessage());
    }    
}
/**
 * 写第第三个工作表的数据
 * @param wsOne
 * @param form
 * @param fb
 */
private void copyWsThree(WritableSheet wsTwo, YwRqQyjyxkForm form,
        YwRqQyjyxkfbForm fb) {
    try {
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"),10);
        WritableCellFormat wcf = new WritableCellFormat(font);
        wcf.setAlignment(Alignment.LEFT);
        wcf.setVerticalAlignment(VerticalAlignment.CENTRE); 
        wcf.setWrap(true);
        wcf.setLocked(true);
        //燃气站登记数据
        List<PzrqgyzdjbForm> pznewdatas=this.getRqssdjbByGyz(form);

        if(pznewdatas!=null &&pznewdatas.size()>18){
            for(int i=18;i<36;i++){
                PzrqgyzdjbForm pzrqss=pznewdatas.get(i);
                this.writeOnePzRowData(wsTwo, 0, i+1,  String.valueOf(1+i), pzrqss, wcf);
            }
        }
        if(pznewdatas!=null &&pznewdatas.size()>36){
            for(int i=36;i<pznewdatas.size();i++){
                PzrqgyzdjbForm pzrqss=pznewdatas.get(i);
                this.writeOnePzRowData(wsTwo, 5, i+1,  String.valueOf(1+i), pzrqss, wcf);
            }
        }
    
 
    } catch (WriteException e) {
        log.error("写入第三个工作表错误"+e.getMessage());
    }
    
}



/**
 * 写第二页燃气设施的数据
 * @param wsOne
 * @param form
 * @param fb
 */
private void writeRqss(WritableSheet wsOne, YwRqQyjyxkForm form,
        YwRqQyjyxkfbForm fb){
    try {
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"),12);
        WritableCellFormat wcf = new WritableCellFormat(font);
        wcf.setAlignment(Alignment.CENTRE);
        wcf.setVerticalAlignment(VerticalAlignment.CENTRE); 
        wcf.setLocked(true);
        List<RqssdjbForm> list=getRqssdjbForm(form,false);
        //将其他的空白锁定
        if(list.size()<18 && list.size()>0){
        	 for(int k=4+list.size();k<=33;k++){
             	ExcelUtil.modiStrCell2(wsOne, 7, k, "", wcf);//
             	ExcelUtil.modiStrCell2(wsOne, 8, k, "", wcf);//
             	ExcelUtil.modiStrCell2(wsOne, 9, k, "", wcf);//
             	ExcelUtil.modiStrCell2(wsOne, 10, k, "", wcf);//
             }
        }
       
        //开始插入数据
        if(list!=null && list.size()>0){
                RqssdjbForm rqss1=list.get(0);
                writeOneSqssRowData(wsOne, 7,4, "1", rqss1, wcf);
                if(list.size()==1)return;
                //没想出怎样用递归方式实现，此处写的不灵活
                RqssdjbForm rqss2=list.get(1);
                writeOneSqssRowData(wsOne, 7,6, "2", rqss2, wcf);
                if(list.size()==2)return;
                RqssdjbForm rqss3=list.get(2);
                writeOneSqssRowData(wsOne, 7,8, "3", rqss3, wcf);
                if(list.size()==3)return;
                RqssdjbForm rqss4=list.get(3);
                writeOneSqssRowData(wsOne, 7,10, "4", rqss4, wcf);
                if(list.size()==4)return;
                RqssdjbForm rqss5=list.get(4);
                writeOneSqssRowData(wsOne, 7,11, "5", rqss5, wcf);
                if(list.size()==5)return;
                RqssdjbForm rqss6=list.get(5);
                writeOneSqssRowData(wsOne, 7,13, "6", rqss6, wcf);
                if(list.size()==6)return;
                RqssdjbForm rqss7=list.get(6);
                writeOneSqssRowData(wsOne, 7,15, "7", rqss7, wcf);
                if(list.size()==7)return;
                RqssdjbForm rqss8=list.get(7);
                writeOneSqssRowData(wsOne, 7,17, "8", rqss8, wcf);
                if(list.size()==8)return;
                RqssdjbForm rqss9=list.get(8);
                writeOneSqssRowData(wsOne, 7,19, "9", rqss9, wcf);
                if(list.size()==9)return;
                RqssdjbForm rqss10=list.get(9);
                writeOneSqssRowData(wsOne, 7,21, "10", rqss10, wcf);
                if(list.size()==10)return;
                RqssdjbForm rqss11=list.get(10);
                writeOneSqssRowData(wsOne, 7,22, "11", rqss11, wcf);
                if(list.size()==11)return;
                 int k=25;//循环12-16行
                 int xh=12;
                 int loopsize=15;
                 if(list.size()>=12 && list.size()<=16)
                     loopsize=list.size();
                 for(int j=11;j<=loopsize;j++){
                     RqssdjbForm rqss=list.get(j);
                     writeOneSqssRowData(wsOne, 7,k, String.valueOf(xh), rqss, wcf);
                     k++;
                     xh++;
                 }
                if(list.size()==16)return;
                RqssdjbForm rqss17=list.get(16);
                writeOneSqssRowData(wsOne, 7,31, "17", rqss17, wcf);
                if(list.size()==17)return;
                RqssdjbForm rqss18=list.get(17);
                writeOneSqssRowData(wsOne, 7,33, "18", rqss18, wcf);
            }
    } catch (Exception e) {
        log.info("写第二页数据出现错误副本"+e.getMessage());
    } 
}

/**
 * 写入单条数据
 * @param wsOne sheet
 * @param col 开始列
 * @param row 行
 * @param index 序号
 * @param rqss
 * @param wcf
 */
private void writeOneSqssRowData(WritableSheet wsOne,int col,int row,String index,
        RqssdjbForm rqss,WritableCellFormat wcf){
    try {
        if(StringUtils.isNotBlank(rqss.getMc()) && StringUtils.isNotBlank(rqss.getDz())){
        	  ExcelUtil.modiStrCell2(wsOne, col, row, index, wcf);//序号
        	  changContentStyle(wsOne, col+1, row,rqss.getMc(),10,14,9,10,12);//注册地址
              //ExcelUtil.modiStrCell2(wsOne, col+1, row, rqss.getMc(), wcf);//名称
               changContentStyle(wsOne, col+2, row,rqss.getDz(),10,14,9,10,12);//注册地址
               if("供应站".equals(rqss.getSslb())){
            	   YwRqRqssylb rqssEntity=ywRqQyjyxkDao.getRqjszdjbByName(rqss.getMc());
            	   String sslb=getGradeBySsdj(rqssEntity);
            	   ExcelUtil.modiStrCell2(wsOne, col+3,row,sslb==null?"":sslb, wcf);//类别等级

               }else{
            	   ExcelUtil.modiStrCell2(wsOne, col+3,row,rqss.getSslb()==null?"":rqss.getSslb(), wcf);//类别
               	}
           }
      
    } catch (Exception e) {
        log.info("写第二页数据出现错误副本"+rqss.getMc()+e.getMessage());
    } 
}

/**
 * 写入单个燃气站的数据
 * @param wsOne
 * @param col
 * @param row
 * @param index
 * @param rqss
 * @param wcf
 */
private void writeOnePzRowData(WritableSheet wsOne,int col,int row,String index,
        PzrqgyzdjbForm pzrqss,WritableCellFormat wcf){
    try {
        ExcelUtil.modiStrCell2(wsOne, col, row, index, wcf);//序号
        ExcelUtil.modiStrCell2(wsOne, col+1, row, pzrqss.getMc(), wcf);//名称
        changContentStyle(wsOne, col+2, row,pzrqss.getDz(),10,14,9,10,12);//注册地址
        ExcelUtil.modiStrCell2(wsOne, col+3,row,pzrqss.getGyzdj()==null?"":pzrqss.getGyzdj(), wcf);//等级
    } catch (Exception e) {
        log.info("写第二页数据出现错误副本"+pzrqss.getMc()+e.getMessage());
    } 
}


/**
 *
 * 改变单元格内的字体包括换行
 * @param ws 工作表
 * @param col 列
 * @param row行
 * @param content 写入的内容
 * @param normlength 正常的长度表示超过多少个字才换行
 * @param newlength 换行的时候每行显示多少字
 * @param onefont 超过两行显示的字大笑
 * @param twofont 只有两行的字
 * @param threefont 默认的字
 */
private void changContentStyle(WritableSheet ws,int col,int row,
        String content,int normlength,int newlength,
        int onefont,int twofont,int threefont){
    try {
        if(StringUtils.isNotBlank(content)){
            int strlength=content.length();
            String otherStr="";
            if(strlength>normlength){//如果超过了normlength个字则换字体大小
                int k=strlength/newlength;
                if(k>0){
                    if(k>2){//超过两行的换字
                        WritableFont font = new WritableFont(WritableFont.createFont("宋体"),onefont);
                        WritableCellFormat wcf = new WritableCellFormat(font);
                        wcf.setAlignment(Alignment.LEFT);
                        wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
                        wcf.setWrap(true);
                        wcf.setLocked(true);
                        ExcelUtil.modiStrCell2(ws, col,row,content, wcf);
                    }else{//超过一行
                        WritableFont font = new WritableFont(WritableFont.createFont("宋体"),onefont);
                        WritableCellFormat wcf = new WritableCellFormat(font);
                        wcf.setAlignment(Alignment.LEFT);
                        wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
                        wcf.setWrap(true);
                        wcf.setLocked(true);
                        ExcelUtil.modiStrCell2(ws, col,row,content, wcf);
                    }
                }else{
                    WritableFont font = new WritableFont(WritableFont.createFont("宋体"),twofont);
                    WritableCellFormat wcf = new WritableCellFormat(font);
                    wcf.setAlignment(Alignment.LEFT);
                    wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
                    wcf.setWrap(true);
                    wcf.setLocked(true);
                    ExcelUtil.modiStrCell2(ws, col,row,content, wcf);
                }
            }else{
                WritableFont font = new WritableFont(WritableFont.createFont("宋体"),threefont);
                WritableCellFormat wcf = new WritableCellFormat(font);
                wcf.setAlignment(Alignment.LEFT);
                wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
                wcf.setLocked(true);
                wcf.setWrap(true);
                ExcelUtil.modiStrCell2(ws, col,row,content, wcf);
            }
        }else{
            WritableFont font = new WritableFont(WritableFont.createFont("宋体"),threefont);
            WritableCellFormat wcf = new WritableCellFormat(font);
            wcf.setAlignment(Alignment.LEFT);
            wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
            wcf.setWrap(true);
            wcf.setLocked(true);
            ExcelUtil.modiStrCell2(ws, col,row,content, wcf);
        }
    } catch (Exception e) {
       log.error(content+"换行错误"+e.getMessage());
    }
}

/**
 * 合并第一个工作表的单元格
 * @param wsOne
 */
private void mergerCells(WritableSheet wsOne) {
    ExcelUtil.mergeCells(wsOne, 0, 1, 4, 1);//企业名称
    ExcelUtil.mergeCells(wsOne, 0, 2, 4, 2);//地址
    ExcelUtil.mergeCells(wsOne, 0, 3, 1, 4);//注册资本合并
    ExcelUtil.mergeCells(wsOne, 0, 5, 4, 6);//供气区域
    ExcelUtil.mergeCells(wsOne, 0, 7, 2,8);//副本编号
    ExcelUtil.mergeCells(wsOne, 0,9, 4,10);//有效期
    ExcelUtil.mergeCells(wsOne, 0,12, 1,13);//营业执照
    ExcelUtil.mergeCells(wsOne, 0,14, 0,15);//法定代表人
    ExcelUtil.mergeCells(wsOne, 2,14, 2,15);//法定代表人职务
    ExcelUtil.mergeCells(wsOne, 4,14, 4,15);//法定代表人职称
    ExcelUtil.mergeCells(wsOne, 0,16, 0,17);//主要负责人
    ExcelUtil.mergeCells(wsOne, 2,16, 2,17);//主要负责人职务
    ExcelUtil.mergeCells(wsOne, 4,16, 4,17);//法主要负责人职称
    ExcelUtil.mergeCells(wsOne, 0,18, 0,19);//安全负责人
    ExcelUtil.mergeCells(wsOne, 2,18, 2,19);//安全负责人职务
    ExcelUtil.mergeCells(wsOne, 4,18, 4,19);//安全负责人职称
    ExcelUtil.mergeCells(wsOne, 0,20, 0,22);//技术负责人
    ExcelUtil.mergeCells(wsOne, 2,20, 2,22);//技术负责人职务
    ExcelUtil.mergeCells(wsOne, 4,20, 4,22);//经营范围职称
    ExcelUtil.mergeCells(wsOne, 0,24, 4,29);//经营范围
    ExcelUtil.mergeCells(wsOne, 2,32, 4,32);//发证机构
    ExcelUtil.mergeCells(wsOne, 3,33, 4,33);//发证日期   
    this.mergerSecodePage(wsOne);
   
}

/**
 * //第二页单元格合并
 * @param wsOne
 */
private void mergerSecodePage(WritableSheet wsOne) {
    ExcelUtil.mergeCells(wsOne, 6, 4, 7, 5);//设施序号1
    ExcelUtil.mergeCells(wsOne, 7, 4, 8, 5);//设施名称2
    ExcelUtil.mergeCells(wsOne, 8, 4, 9, 5);//设施地址3
    ExcelUtil.mergeCells(wsOne, 9, 4, 10, 5);//设施类别4
    
    ExcelUtil.mergeCells(wsOne, 6, 6, 7, 7);//设施序号1
    ExcelUtil.mergeCells(wsOne, 7, 6, 8, 7);//设施名称2
    ExcelUtil.mergeCells(wsOne, 8, 6, 9, 5);//设施地址3
    ExcelUtil.mergeCells(wsOne, 9,6, 10,7);//设施类别4
    
    ExcelUtil.mergeCells(wsOne, 6, 8, 7, 9);//设施序号1
    ExcelUtil.mergeCells(wsOne, 7, 8, 8, 9);//设施名称2
    ExcelUtil.mergeCells(wsOne, 8, 8, 9, 9);//设施地址3
    ExcelUtil.mergeCells(wsOne, 9,8, 10,9);//设施类别4

    ExcelUtil.mergeCells(wsOne, 7, 10, 7,10);//设施序号1
    ExcelUtil.mergeCells(wsOne, 8, 10, 8, 10);//设施名称2
    ExcelUtil.mergeCells(wsOne, 9, 10, 9, 10);//设施地址3
    ExcelUtil.mergeCells(wsOne, 6,10, 10,10);//设施类别4
    
    ExcelUtil.mergeCells(wsOne, 7, 12, 7, 12);//设施序号1
    ExcelUtil.mergeCells(wsOne, 8, 12, 8, 12);//设施名称2
    ExcelUtil.mergeCells(wsOne, 9, 12, 9, 12);//设施地址3
    ExcelUtil.mergeCells(wsOne, 6,12, 10,12);//设施类别4

    
    ExcelUtil.mergeCells(wsOne, 7, 13, 7, 14);//设施序号1
    ExcelUtil.mergeCells(wsOne, 8, 13, 8, 14);//设施名称2
    ExcelUtil.mergeCells(wsOne, 9, 13, 9, 14);//设施地址3
    ExcelUtil.mergeCells(wsOne, 6,13, 10,14);//设施类别4
    
    ExcelUtil.mergeCells(wsOne, 7, 15, 7, 16);//设施序号1
    ExcelUtil.mergeCells(wsOne, 8, 15, 8, 16);//设施名称2
    ExcelUtil.mergeCells(wsOne, 9, 15, 9, 16);//设施地址3
    ExcelUtil.mergeCells(wsOne, 6,15, 10,16);//设施类别4
    
    ExcelUtil.mergeCells(wsOne, 7, 17, 7, 18);//设施序号1
    ExcelUtil.mergeCells(wsOne, 8, 17, 8, 18);//设施名称2
    ExcelUtil.mergeCells(wsOne, 9, 17, 9, 18);//设施地址3
    ExcelUtil.mergeCells(wsOne, 6,17, 10,18);//设施类别4

    ExcelUtil.mergeCells(wsOne, 7, 19, 7, 20);//设施序号1
    ExcelUtil.mergeCells(wsOne, 8, 19, 8, 20);//设施名称2
    ExcelUtil.mergeCells(wsOne, 9, 19, 9, 20);//设施地址3
    ExcelUtil.mergeCells(wsOne, 6,19, 10,20);//设施类别4
    
    ExcelUtil.mergeCells(wsOne, 7, 21, 7, 21);//设施序号1
    ExcelUtil.mergeCells(wsOne, 8, 21, 8, 21);//设施名称2
    ExcelUtil.mergeCells(wsOne, 9, 21, 9, 21);//设施地址3
    ExcelUtil.mergeCells(wsOne, 6,21, 10,21);//设施类别4
    
    ExcelUtil.mergeCells(wsOne, 7, 23, 7, 24);//设施序号1
    ExcelUtil.mergeCells(wsOne, 8, 23, 8, 24);//设施名称2
    ExcelUtil.mergeCells(wsOne, 9, 23, 9, 24);//设施地址3
    ExcelUtil.mergeCells(wsOne, 6,23, 10,24);//设施类别4
    
    for(int k=25;k<=28;k++){
        ExcelUtil.mergeCells(wsOne, 7,k, 7, k);//设施序号1
        ExcelUtil.mergeCells(wsOne, 8,k, 8, k);//设施名称2
        ExcelUtil.mergeCells(wsOne, 9, k, 9, k);//设施地址3
        ExcelUtil.mergeCells(wsOne, 6,k, 10,k);//设施类别4
    }
 
    ExcelUtil.mergeCells(wsOne, 7, 29, 7, 30);//设施序号1
    ExcelUtil.mergeCells(wsOne, 8, 29, 8, 30);//设施名称2
    ExcelUtil.mergeCells(wsOne, 9, 29, 9, 30);//设施地址3
    ExcelUtil.mergeCells(wsOne, 6,29, 10,30);//设施类别4
    
    ExcelUtil.mergeCells(wsOne, 7, 31, 7, 32);//设施序号1
    ExcelUtil.mergeCells(wsOne, 8, 31, 8, 32);//设施名称2
    ExcelUtil.mergeCells(wsOne, 9, 31, 9, 32);//设施地址3
    ExcelUtil.mergeCells(wsOne, 6,31, 10,32);//设施类别4
    
    ExcelUtil.mergeCells(wsOne, 7, 33, 7, 33);//设施序号1
    ExcelUtil.mergeCells(wsOne, 8, 33, 8, 33);//设施名称2
    ExcelUtil.mergeCells(wsOne, 9, 33, 9, 33);//设施地址3
    ExcelUtil.mergeCells(wsOne, 6,33, 10,33);//设施类别4
}


/**
 * 写数据
 */
public String writeData(YwRqQyjyxkForm form, YwRqQyjyxkfbForm fb, String context) {
    String inpath=context+"/Templates/rqjyxk/rqjyxkfb.xls";
    String nodeStr = System.getProperty("file.separator"); 
    inpath=inpath.replace("/",nodeStr);
    String tempOutPath="Templates/rqjyxk/outfile";
    String path="";
    try {
        path = this.writeDataPath(inpath, tempOutPath, context, form, fb);//exportExcel(inpath, tempOutPath, context, form, fb);
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }//writeDataPath(inpath, tempOutPath,context,form,fb);
    return  path;
}


/**
 * 写数据并放回文件目录
 * @param inpath
 * @param realpath
 * @param context
 * @param form
 * @param fb
 * @return
 */
private String writeDataPath(String inpath,String realpath,String context,YwRqQyjyxkForm form, YwRqQyjyxkfbForm fb){
    String fileName = GetFileName.getFileName("xls");
    String dateDir = GetFileName.getDirector();
    String returnpath=realpath+"/"+dateDir+"/"+fileName;
    String path=context+"/"+returnpath;
    path=path.replace("/",File.separator);
    String outdir=path.substring(0,path.lastIndexOf(File.separator));
    File file=new File(outdir);
    if(!file.exists()){
        file.mkdirs();
    }
   writerDBDataToExcel(inpath,path,form,fb);
	String dir=path.substring(0,path.lastIndexOf(File.separator));
	String filename=path.substring(path.lastIndexOf(File.separator)+1,path.length());
	try {//删除其他文件
		FileUtil.delDir(dir,filename);
	} catch (IOException e) {
		log.error("删除文件错误"+e);
	}
    return returnpath;
}


private Map<String,String> createBaseMap(YwRqQyjyxkForm form, YwRqQyjyxkfbForm fb){
    Map<String, String> map=new HashMap<String,String>();
    map.put("qymc", fb.getQymc());
    map.put("zcdz", fb.getZcdz());
    map.put("zczb", fb.getZczb());
    map.put("gqqy",fb.getGqqy());
    map.put("xkzbh",fb.getXkzbh());
    map.put("xkzyxq", fb.getXkzyxq());
    map.put("yyzzzch", fb.getYyzzzch());
    map.put("fddbr", form.getFddbr());
    map.put("fddbrZw",form.getFddbrZw());
    map.put("fddbrZc", form.getFddbrZc());
    map.put("zyfzr",form.getZyfzr());
    map.put("zyfzrZw",form.getZyfzrZw());
    map.put("zyfzrZc",form.getZyfzrZc());
    map.put("aqfzr", form.getAqfzr());
    map.put("aqfzrZw",form.getAqfzrZw());
    map.put("aqfzrZc",form.getAqfzrZc());
    map.put("jsfzr", form.getJsfzr());
    map.put("jsfzrZw",form.getJsfzrZw());
    map.put("jsfzrZc",form.getJsfzrZc());
    map.put("jyfw",fb.getJyfw());
    map.put("fzjg",fb.getFzjg());
    map.put("fzrq",fb.getFzrq());
    return map;
}

/**
 * 
 */
public String writeXkzData(YwRqQyjyxkForm form, String context) {
    String inpath=context+"/Templates/rqjyxk/rqjyxk.xls";
    String nodeStr = System.getProperty("file.separator"); 
    inpath=inpath.replace("/",nodeStr);
    String tempOutPath="Templates/rqjyxk/outfile";
    String path="";
    try {
        path = this.writeXkzExcelData(inpath, tempOutPath, context, form);
        
    } catch (Exception e) {
        e.printStackTrace();
    }//writeDataPath(inpath, tempOutPath,context,form,fb);
    String paths=context+"/"+path;
    paths=paths.replace("/",File.separator);
	String dir=paths.substring(0,paths.lastIndexOf(File.separator));
	String filename=paths.substring(paths.lastIndexOf(File.separator)+1,paths.length());
	try {//删除其他文件
		FileUtil.delDir(dir,filename);
	} catch (IOException e) {
		log.error("删除文件错误"+e);
	}
    return  path;

}

/**
 * 生成正本的路径
 * @param inpath
 * @param realpath
 * @param tempOutPath
 * @param context
 * @param form
 * @return
 */
private String writeXkzExcelData(String inpath,String realpath,
        String context, YwRqQyjyxkForm form) {
    String fileName = GetFileName.getFileName("xls");
    String dateDir = GetFileName.getDirector();
    String returnpath=realpath+"/"+dateDir+"/"+fileName;
    String path=context+"/"+returnpath;
    path=path.replace("/",File.separator);
    String outdir=path.substring(0,path.lastIndexOf(File.separator));
    File file=new File(outdir);
    if(!file.exists()){
        file.mkdirs();
    }
    writerDBDataToXkzExcel(inpath,path,form);
    return returnpath;
}

/**
 * 写入正本数据
 * @param inpath
 * @param path
 * @param form
 */
private void writerDBDataToXkzExcel(String inpath, String path,
        YwRqQyjyxkForm form) {
    WritableWorkbook wwb;
    try {
        wwb = ExcelUtil.createWorkbookByOld(inpath, path);
        WritableSheet wsOne = ExcelUtil.getWritableSheetByIndex(wwb, 0);
        copyXkData(wsOne,form);
        wsOne.setProtected(true);
        wwb.write();
        wwb.close();
    } catch (Exception e) {
        log.error("写文件错误"+e);
    } 

}

/**
 * 写入经营许可数据
 * @param wsOne
 * @param form
 */
private void copyXkData(WritableSheet wsOne, YwRqQyjyxkForm form) {
    WritableFont font = new WritableFont(WritableFont.createFont("宋体"),20);
    WritableCellFormat wcf = new WritableCellFormat(font);
    try {
        wcf.setAlignment(Alignment.LEFT);
        wcf.setVerticalAlignment(VerticalAlignment.CENTRE); 
        wcf.setLocked(true);
        //wcf.setWrap(true);
        WritableFont font1 = new WritableFont(WritableFont.createFont("宋体"),14);
        WritableCellFormat wcf1 = new WritableCellFormat(font1);
        wcf1.setAlignment(Alignment.CENTRE);
        wcf1.setVerticalAlignment(VerticalAlignment.CENTRE);  
        wcf1.setLocked(true);  
        WritableFont font6= new WritableFont(WritableFont.createFont("宋体"),14);
        WritableCellFormat wcf6 = new WritableCellFormat(font6);
        wcf6.setAlignment(Alignment.LEFT);
        wcf6.setVerticalAlignment(VerticalAlignment.CENTRE);  
        wcf6.setLocked(true); 
        
        WritableFont font2 = new WritableFont(WritableFont.createFont("宋体"),18);
        WritableCellFormat wcf2 = new WritableCellFormat(font2);
        wcf2.setAlignment(Alignment.LEFT);
        wcf2.setVerticalAlignment(VerticalAlignment.CENTRE);
        WritableFont font3 = new WritableFont(WritableFont.createFont("宋体"),14);
        WritableCellFormat wcf3= new WritableCellFormat(font3);
        wcf3.setAlignment(Alignment.CENTRE);
        wcf3.setVerticalAlignment(VerticalAlignment.CENTRE);
        wcf3.setLocked(true);
        
        WritableFont font4 = new WritableFont(WritableFont.createFont("宋体"),12);
        WritableCellFormat wcf4= new WritableCellFormat(font4);
        wcf4.setAlignment(Alignment.CENTRE);
        wcf4.setVerticalAlignment(VerticalAlignment.CENTRE);
        wcf4.setLocked(true);
        ExcelUtil.modiStrCell2(wsOne, 11, 1, form.getZsbh()==null?"":form.getZsbh(), wcf6);//编号
        ExcelUtil.modiStrCell2(wsOne, 1, 3, form.getSqdw()==null?"":form.getSqdw(), wcf);//申请单位
        this.changContentStyle(wsOne, 1, 5, form.getAddress()==null?"":form.getAddress(), 34, 52, 10, 14, 22);
        ExcelUtil.modiStrCell2(wsOne, 1, 7, form.getJyfw()==null?"":form.getJyfw(), wcf);//经营范围
        changContentStyle(wsOne, 1,9, form.getGqqy()==null?"":form.getGqqy(),34, 52, 10, 14, 22);//供气区域
        ExcelUtil.modiStrCell2(wsOne, 1, 11, form.getFddbr()==null?"":form.getFddbr(), wcf);//负责人
        ExcelUtil.modiStrCell2(wsOne, 2, 13, form.getEndyear()==null?"":form.getEndyear(), wcf1);//结束年份
        ExcelUtil.modiStrCell2(wsOne, 4, 13, form.getEndmonth()==null?"":form.getEndmonth(), wcf1);//开始时间
        if(StringUtils.isNotBlank(form.getEndday()) &&  form.getEndday().length()==3){
     	   ExcelUtil.modiStrCell2(wsOne, 6, 13, form.getEndday()==null?"":form.getEndday(), wcf4);//开始时间
        }else{
     	   ExcelUtil.modiStrCell2(wsOne, 6, 13, form.getEndday()==null?"":form.getEndday(), wcf1);//开始时间
        }
        ExcelUtil.modiStrCell2(wsOne, 8, 16, form.getFzjg()==null?"广州市城市管理委员会":form.getFzjg(), wcf2);//发证机关
        ExcelUtil.modiStrCell2(wsOne, 8, 18, form.getStartyear()==null?"":form.getStartyear(), wcf3);//开始时间
        ExcelUtil.modiStrCell2(wsOne, 10, 18, form.getStartmonth()==null?"":form.getStartmonth(), wcf3);//开始时间
       if(StringUtils.isNotBlank(form.getStartday()) &&  form.getStartday().length()==3){
    	   ExcelUtil.modiStrCell2(wsOne, 13, 18, form.getStartday()==null?"":form.getStartday(), wcf4);//发证日期
       }else{
    	   ExcelUtil.modiStrCell2(wsOne, 13, 18, form.getStartday()==null?"":form.getStartday(), wcf3);//发证日期   
       }
     
    } catch (Exception e) {
        log.error("写入设施许可证错误"+e.getMessage());
    } 
}

/*
 * (non-Javadoc)
 * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#writeSslybData(com.augurit.gzcgwsp.rqqyjyxk.web.form.YwRqQyjyxkForm, java.lang.String)
 */
public String writeSslybData(YwRqRqssylb form, String context,YwRqQyjyxkForm xkForm) {
    String inpath=context+"/Templates/rqjyxk/rqssbs.xls";
    String nodeStr = System.getProperty("file.separator"); 
    inpath=inpath.replace("/",nodeStr);
    String tempOutPath="Templates/rqjyxk/outfile/rqssbs/outfile";
    String path="";
    try {
        path = this.writeSsbsExcelData(inpath, tempOutPath, context, form,xkForm);
        String paths=context+"/"+path;
        paths=paths.replace("/", File.separator);
    	String dir=paths.substring(0,paths.lastIndexOf(File.separator));
    	String filename=paths.substring(paths.lastIndexOf(File.separator)+1,paths.length());
    	try {//删除其他文件
    		FileUtil.delDir(dir,filename);
    	} catch (IOException e) {
    		log.error("删除文件错误"+e);
    	}
    } catch (Exception e) {
      log.error("写入数据设施一览表错误"+e);
    }
    return  path;
}

/**
 * 写设施许可标识数据
 * @param inpath
 * @param tempOutPath
 * @param context
 * @param form
 * @return
 */
private String writeSsbsExcelData(String inpath, String realpath,
        String context, YwRqRqssylb form,YwRqQyjyxkForm xkForm) {
        String fileName = GetFileName.getFileName("xls");
        String dateDir = GetFileName.getDirector();
        String returnpath=realpath+"/"+dateDir+"/"+fileName;
        String path=context+"/"+returnpath;
        path=path.replace("/",File.separator);
        String outdir=path.substring(0,path.lastIndexOf(File.separator));
        File file=new File(outdir);
        if(!file.exists()){
            file.mkdirs();
        }
        writerSsbsDataToExcel(inpath,path,form,xkForm);
       return returnpath;
}
/**
 * 写设施许可标识数据
 * @param inpath
 * @param tempOutPath
 * @param context
 * @param form
 * @return
 */
private void writerSsbsDataToExcel(String inpath, String path,
        YwRqRqssylb form,YwRqQyjyxkForm xkForm) {
    WritableWorkbook wwb;
    try {
        wwb = ExcelUtil.createWorkbookByOld(inpath, path);
        WritableSheet wsOne = ExcelUtil.getWritableSheetByIndex(wwb, 0);
        copyssbsData(wsOne,form, xkForm);
        wsOne.setProtected(true);
        wwb.write();
        wwb.close();
    } catch (Exception e) {
        log.error("写文件错误"+e);
    } 
    
}

/**
 * 写数据到模版中
 * @param wsOne
 * @param form
 */
private void copyssbsData(WritableSheet wsOne, YwRqRqssylb form, YwRqQyjyxkForm xkForm) {
	 try {
	        WritableFont font = new WritableFont(WritableFont.createFont("宋体"),18);
	        WritableCellFormat wcf = new WritableCellFormat(font);
	        wcf.setAlignment(Alignment.LEFT);
	        wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
	        
	        WritableFont font1 = new WritableFont(WritableFont.createFont("宋体"),12);
	        WritableCellFormat wcf1 = new WritableCellFormat(font1);
	        wcf1.setAlignment(Alignment.CENTRE);
	        wcf1.setVerticalAlignment(VerticalAlignment.CENTRE);   
	        Calendar cal=Calendar.getInstance();
	        int year=cal.get(Calendar.YEAR);
	        int month=cal.get(Calendar.MONTH)+1;
	        int day=cal.get(Calendar.DATE);
	        String fzorg=form.getFzorg()==null?"广州市城市管理委员会":form.getFzorg();
	        String startyear="";
	        String startmonth="";
	        String starday="";
	        String endyear="";
	        String endmonth="";
	        String endday="";
	        if(StringUtils.isNotBlank(form.getFzdate())){
	        	String fzdate=form.getFzdate();
	        	if(fzdate.length()>=10){
	        		startyear=DateUtilsEX.convertYearToChar(Integer.valueOf(fzdate.substring(0,4)));
	        		startmonth=DateUtilsEX.convertMonthToChar(Integer.valueOf(fzdate.substring(5,7)));
	        		starday=DateUtilsEX.convertDayToChar(Integer.valueOf(fzdate.substring(8,10)));
	        	}
	        }else{
	        	startyear=xkForm.getStartyear()==null?DateUtilsEX.convertYearToChar(year):xkForm.getStartyear();
	        	startmonth=xkForm.getStartmonth()==null?DateUtilsEX.convertMonthToChar(month):xkForm.getStartmonth();
        		starday=xkForm.getStartday()==null?DateUtilsEX.convertDayToChar(day):xkForm.getStartday();
	        }
	        if(StringUtils.isNotBlank(form.getValiddate())){//有效期
	        	String validdate=form.getValiddate();
	        	if(validdate.length()>=10){
	        		endyear=DateUtilsEX.convertYearToChar(Integer.valueOf(validdate.substring(0,4)));
	        		endmonth=DateUtilsEX.convertMonthToChar(Integer.valueOf(validdate.substring(5,7)));
	        		endday=DateUtilsEX.convertDayToChar(Integer.valueOf(validdate.substring(8,10)));
	        	}
	        }else{
	        	endyear=xkForm.getStartyear()==null?DateUtilsEX.convertYearToChar(year):xkForm.getEndyear();
	        	endmonth=xkForm.getStartmonth()==null?DateUtilsEX.convertMonthToChar(month):xkForm.getEndmonth();
	        	endday=xkForm.getStartday()==null?DateUtilsEX.convertDayToChar(day):xkForm.getEndday();
	        }
	        
	        ExcelUtil.modiStrCell2(wsOne, 12, 1, form.getBszsbh()==null?"":form.getBszsbh(), wcf1);//编号
	        ExcelUtil.modiStrCell2(wsOne, 2, 4, form.getSsmc()==null?"":form.getSsmc(), wcf);//申请单位
	        ExcelUtil.modiStrCell2(wsOne, 2, 6, form.getSsaddress()==null?"":form.getSsaddress(), wcf);//地址
	        ExcelUtil.modiStrCell2(wsOne, 2, 8, form.getFzr()==null?"":form.getFzr(), wcf);//负责人
	        ExcelUtil.modiStrCell2(wsOne, 3, 10,endyear, wcf1);//有效期
	        ExcelUtil.modiStrCell2(wsOne, 5, 10,endmonth, wcf1);//月
	        ExcelUtil.modiStrCell2(wsOne, 7, 10,endday, wcf1);//日
	        ExcelUtil.modiStrCell2(wsOne, 11, 16, fzorg, wcf);//发证机关
	        ExcelUtil.modiStrCell2(wsOne, 11, 18,  startyear,wcf1);//颁发日期
	        ExcelUtil.modiStrCell2(wsOne, 13, 18, startmonth, wcf1);//开始时间
	        ExcelUtil.modiStrCell2(wsOne, 15, 18, starday, wcf1);//开始时间
	        
	    } catch (Exception e) {
	        log.error("写入设施一览表的数据错误"+e);
	    } 
	    
    
}

/*
 * (non-Javadoc)
 * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#checkPerson(java.lang.String, java.lang.Long, java.lang.Long)
 */
public String checkPerson(String contextpath,String templateCode, Long id, Long qyid) {
	 String result="";
	 Long tempId=getTempId(templateCode);
	 List<YwComSjclForm> sjclTpl = getFiles(qyid, Long.valueOf(id), templateCode);
     String filepath=this.getFilePath(sjclTpl,tempId,contextpath);
     if(StringUtils.isNotBlank(filepath)){
         filepath=contextpath+filepath;
         filepath=filepath.replace("/",File.separator);
         result+=ywRqQyjyxkImportservice.checkPersonData(filepath,qyid);
     }else{
         result+="收件材料没有符合条件的文件\n";
         
     }

	return result;
}

/**
 * 入库的操作
 */
public void doUpdate(YwRqQyjyxkForm jyxk) {
	Long id=jyxk.getId();
	if(jyxk != null && jyxk.getId() != null){
		YwRqQyjyxk entity = ywRqQyjyxkDao.getEntity(jyxk.getId());
		//保存企业信息
        YwComQyxxForm qyxxForm = new YwComQyxxForm();
        if(jyxk.getQyid() == null){
        	if(StringUtils.isNotBlank(jyxk.getSqdw())){
            	YwComQyxxForm ywComQyxxForm = ywComQyxxService.getFormBySqdw(jyxk.getSqdw(), jyxk.getTemplateCode());
            	if(ywComQyxxForm != null){
            		qyxxForm = ywComQyxxForm;
            	}
            }
        }
        YwRqQyjyxkConvertor.convertFormToQyxxForm(jyxk, qyxxForm);
        qyxxForm.setTemplateCode(jyxk.getTemplateCode());
        Long ids=qyxxForm.getId();
        ywComQyxxService.update(qyxxForm);        
         jyxk.setQyid(qyxxForm.getId());
		  entity.setSqdw(jyxk.getSqdw());
		  //entity.setAddress(jyxk.getAddress());
			entity.setClsj(jyxk.getClsj());
			entity.setYyzzzch(jyxk.getYyzzzch());
			entity.setLxr(jyxk.getLxr());
			entity.setLxdh(jyxk.getLxdh());
			entity.setDzyx(jyxk.getDzyx());
			entity.setCz(jyxk.getCz());
			entity.setQywz(jyxk.getQywz());
		   entity.setFddbr(jyxk.getFddbr());
		   entity.setFddbrZw(jyxk.getFddbrZw());
		   entity.setFddbrZc(jyxk.getFddbrZc());
		   entity.setZyfzr(jyxk.getZyfzr());
		   entity.setZyfzrZc(jyxk.getZyfzrZc());
		   entity.setZyfzrZw(jyxk.getZyfzrZw());
		   entity.setJsfzr(jyxk.getJsfzr());
		   entity.setJsfzrZc(jyxk.getJsfzrZc());
		   entity.setJsfzrZw(jyxk.getJsfzrZw());
		  // entity.setZczj(jyxk.getZczj());
		   entity.setQylx(jyxk.getQylx());
		   entity.setJyfw(jyxk.getJyfw());
		   entity.setGqqy(jyxk.getGqqy());
		   entity.setFwdh(jyxk.getFwdh());
		   entity.setQxdh(jyxk.getQxdh());
		   entity.setQyjj(jyxk.getQyjj());
	   ywRqQyjyxkDao.save(entity);
	}
       
	
}

/*
 * 生成正本的证书编号(non-Javadoc)
 * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#createCardNo(com.augurit.gzcgwsp.rqqyjyxk.web.form.YwRqQyjyxkForm, int)
 */
public String createCardNo(YwRqQyjyxkForm form, int type) {
//	String sql=" select count(*) from YwRqQyjyxk where zsbh is not null and id not in ("
//	+" (select min(id) from YwRqQyjyxk where zsbh is not null group  by zsbh having count(*) >= 1) )";
	//此处以后需要去除重复证书编号，因为有写企业只是申请燃气设施
	String sql="select count(*) from YwRqQyjyxk where zsbh is not null";
	List list=ywRqQyjyxkDao.find(sql);
	String NO="01-0001";
	Long num=(Long) list.get(0);
	if(num!=null){
		num=num+1;
	}else{
		num=0l;
	}
	String subfix="";
	if(num<999){
		for(int i=0;i<3-String.valueOf(num).length();i++){
			subfix+="0";
		}
	}
	NO+="-"+subfix+num;
	return NO;
}

/*
 * (non-Javadoc)
 * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#getEntityBySqdw(java.lang.String)
 */
@Override
public List<YwRqQyjyxk> getEntityBySqdw(String sqdw) {
	String sql=" from YwRqQyjyxk  where sqdw='"+sqdw+"'";
	List<YwRqQyjyxk> list=this.ywRqQyjyxkDao.find(sql);
	return list;
}

public String wfBusYslTuih(YwRqQyjyxkForm form,LoginUserForm loginUser) throws Exception {
	String  loginUserName = loginUser.getUser().getUserName();
	WfBusInstanceTaskForm taskForm = new WfBusInstanceTaskForm();
	taskForm.setHandleComments("资料不全，不予受理。");
	//把审批状态数据保存到ywWebserviceInfo ，交换给方欣
	ywWebserviceInfoService.saveYwWebserviceInfo(form.getSqrq(), form.getAcceptNo(), form.getCbyw(), form.getYwlxr(), form.getSjr(), form.getSqdw(), 
			form.getBusinessCode(), form.getApprovalNo(), loginUserName, loginUser.getOrg().getOrgName(), "ysl", new Date(), false, "预受理", taskForm);
//	System.out.println("form.getYslThyy() = "+java.net.URLDecoder.decode(form.getYslThyy(),"UTF-8"));
	this.changeYslState(form.getId(), 0L,taskForm.getHandleComments());
	
	//（网办）交换到市民网页
	Map map=exchangeStateService.buildMap(loginUser, null, form.getId().toString(), form.getTemplateCode(), false);
	String durationExpression = wfServiceEX.getDurationExpressionByTemplateCode(form.getTemplateCode());
	ywWebserviceForWbService.setValueAndSaveYwWebserviceForWb(loginUserName,form.getTemplateCode(),
			form.getId().toString(),durationExpression,"001",taskForm.getHandleComments(), map);
	return null;
}

	public void getSslbUrl(String contextPath){
		String sql = "select t.filepath from YW_RQ_RQSSYLB t where t.sslb='供应站' and t.sskh =3";
		List<String> list = ywRqQyjyxkDao.getSession().createSQLQuery(sql).list();
		if(list != null && list.size() > 0){
			System.out.println("list.size() ================================ "+list.size());
			for(int i = 0;i <list.size();i++){
				String url = contextPath+File.separator+list.get(i);
				try {
					InputStream istream=new FileInputStream(url);
					// 通过得到的文件输入流inputstream创建一个HSSFWordbook对象
					HSSFWorkbook hssfworkbook = new HSSFWorkbook(istream);// 将is流实例到
					// 一个excel流里
					HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);// 第一个工作表
					HSSFRow hssfrow = hssfsheet.getRow(3);// 第一行
					String aa = "";
					if (hssfrow.getCell((short) 3) == null) {//注册地址
						aa = "";
					} else if (hssfrow.getCell((short) 3).getCellType() == 0) {
						aa = new Double(hssfrow.getCell((short) 3).getNumericCellValue()).toString();
					} else {// 如果EXCEL表格中的数据类型为字符串型
						aa = hssfrow.getCell((short) 3).getStringCellValue().trim();
					}
					System.out.println("url ================================ "+url);
					System.out.println("主要道路 ================================ "+aa);
					hssfrow.getCell((short) 3).setCellValue(8.0);
					try
					  {
					    FileOutputStream fout=new FileOutputStream(url);
					    hssfworkbook.write(fout);
					    //储存
					    fout.close();
					    
					  }
					  catch(IOException e)
					  {
					    e.printStackTrace();
					  }

					
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
			
		}
	}

	/*
	 * 追加打印的文件,追加暂时只是只追加18条记录，即打印副本的预览的第二页，一般一次最多审批也就18个站
	 * @see com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkService#writeData(com.augurit.gzcgwsp.rqqyjyxk.web.form.YwRqQyjyxkForm, com.augurit.gzcgwsp.rqqyjyxk.web.form.YwRqQyjyxkfbForm, java.lang.String, java.util.Map, java.util.Map)
	 */
	@Override
	public String writeData(YwRqQyjyxkForm form, YwRqQyjyxkfbForm fb,
			String context, Map<String, List<RqssdjbForm>> rqssMap,
			Map<String, List<PzrqgyzdjbForm>> pzMap) {
		String inpath=context+"/Templates/rqjyxk/rqjyxkfb.xls";
		    String nodeStr = System.getProperty("file.separator"); 
		    inpath=inpath.replace("/",nodeStr);
		    String tempOutPath="Templates/rqjyxk/outfile";
		    String path="";
		    try {
		        path = this.writeDataPath(inpath, tempOutPath, context, form, fb ,rqssMap,pzMap);//exportExcel(inpath, tempOutPath, context, form, fb);
		    } catch (Exception e) {
		        // TODO Auto-generated catch block
		        e.printStackTrace();
		    }//writeDataPath(inpath, tempOutPath,context,form,fb);
		    return  path;
		
	}
	
   /**
    * 生成打印的文件
    * @param inpath
    * @param tempOutPath
    * @param context
    * @param form
    * @param fb
    * @param rqssMap
    * @param pzMap
    * @return
    */
	private String writeDataPath(String inpath, String realpath,
			String context, YwRqQyjyxkForm form, YwRqQyjyxkfbForm fb,
			Map<String, List<RqssdjbForm>> rqssMap,
			Map<String, List<PzrqgyzdjbForm>> pzMap) {
		 	List<RqssdjbForm> list=this.getRqssdjbForm(form,true);
		 	 String fileName = GetFileName.getFileName("xls");
		     String dateDir = GetFileName.getDirector();
		     String returnpath=realpath+"/"+dateDir+"/"+fileName;
		     String path=context+"/"+returnpath;
		     path=path.replace("/",File.separator);
		     String outdir=path.substring(0,path.lastIndexOf(File.separator));
		     File file=new File(outdir);
		     if(!file.exists()){
		         file.mkdirs();
		     }
		    writerDBDataToExcel(inpath,path,form,fb,list);
		 	String dir=path.substring(0,path.lastIndexOf(File.separator));
		 	String filename=path.substring(path.lastIndexOf(File.separator)+1,path.length());
		 	try {//删除其他文件
		 		FileUtil.delDir(dir,filename);
		 	} catch (IOException e) {
		 		log.error("删除文件错误"+e);
		 	}
		     return returnpath;
	}
	
	/**
	 * 开始写文件
	 * @param inFilePath
	 * @param outFilePath
	 * @return
	 */
	private String writerDBDataToExcel(String inFilePath, String outFilePath,YwRqQyjyxkForm form, YwRqQyjyxkfbForm fb,List<RqssdjbForm> list){
	    WritableWorkbook wwb;
	    try {
	        wwb = ExcelUtil.createWorkbookByOld(inFilePath, outFilePath);
	        WritableSheet wsOne = ExcelUtil.getWritableSheetByIndex(wwb, 0);
	        WritableSheet wsTwo = ExcelUtil.getWritableSheetByIndex(wwb, 1);
	        WritableSheet wsThree = ExcelUtil.getWritableSheetByIndex(wwb, 2);
	        WritableFont font = new WritableFont(WritableFont.createFont("宋体"),12);
	        WritableCellFormat wcf = new WritableCellFormat(font);
	        wcf.setAlignment(Alignment.CENTRE);
	        wcf.setVerticalAlignment(VerticalAlignment.CENTRE); 
	        wcf.setLocked(true);
	        for(RqssdjbForm rqss:list){
	        	String pagenum=rqss.getPagenum();
	        	String row=rqss.getRownum();
	        	if(StringUtils.isNotBlank(pagenum)){
	        		int rownum=Integer.valueOf(row);
	        		int page=Integer.valueOf(pagenum);
	        		if(page==2){//第二页追加
	        			writeOneSqssRowData(wsOne, 7,getRowsByPage(rownum,page), row, rqss, wcf);
	        		}else if(page==3){//第三,第四页追加
	        			writeOneSqssRowData(wsTwo, 0,rownum, row, rqss, wcf);
	        		}else if(page==4){//
	        			writeOneSqssRowData(wsTwo,5,rownum,row, rqss, wcf);
	        		}else if(page==5){//第5页
	        			writeOneSqssRowData(wsThree, 0,rownum,row, rqss, wcf);
	        		}else if(page==6){//最后页
	        			writeOneSqssRowData(wsThree, 5,rownum,row, rqss, wcf);
	        		}
	        	}
	        }
	        wsOne.setProtected(true);
	        wsTwo.setProtected(true);
	        wsThree.setProtected(true);
	        wwb.write();
	        wwb.close();
	    } catch (Exception e) {
	        log.error("写文件错误"+e);
	    } 
	    
	    return outFilePath;
	}

	/**
	 * 获取是从模版的哪行写
	 * @return
	 */
	private int getRowsByPage(int row,int pagenum){
		int rownum=0;
		//第二页的模版不同
			if(row==1){
				rownum=4;
			}else if(row==2){
				rownum=6;
			}else if(row==3){
				rownum=8;
			}else if(row==4){
				rownum=10;
			}else if(row==5){
				rownum=11;
			}else if(row==6){
				rownum=13;
			}else if(row==7){
				rownum=15;
			}else if(row==8){
				rownum=17;
			}else if(row==9){
				rownum=19;
			}else if(row==10){
				rownum=21;
			}else if(row==11){
				rownum=22;
			}else if(row==12){
				rownum=25;
			}else if(row==13){
				rownum=26;
			}else if(row==14){
				rownum=27;
			}else if(row==15){
				rownum=28;
			}else if(row==16){
				rownum=29;
			}else if(row==17){
				rownum=31;
			}else if(row==18){
				rownum=33;
			}
			return rownum;
	}

	@Override
	public void updateAcceptNo(YwRqQyjyxk entity) {
		String hql = "update YwRqQyjyxk gd set gd.acceptNo='"+entity.getAcceptNo()+"',gd.serialNo='"+entity.getAcceptNo()+"' where gd.id=:id";
		Query query =ywRqQyjyxkDao.getSession().createQuery(hql);
		query.setLong("id", entity.getId());
		query.executeUpdate();
		
	}

	@Override
	public void buildZsbh(YwRqQyjyxkForm form,String ssylbid,YwRqRqssylb entity) {
	            YwRqQyjyxk jyxk=ywRqQyjyxkDao.getEntity(form.getId());
	            Long sslybnum=getNumSsxk(form.getId(),Long.valueOf(ssylbid));
	            String jyxkbh="";
	            if(jyxk!=null){
	                jyxkbh=jyxk.getZsbh()==null?"":jyxk.getZsbh();
	                if(StringUtils.isNotBlank(jyxkbh)){
	                    jyxkbh=form.getZsbh();
	                }else{
	                	String sql=" from YwRqQyjyxk t where t.zsbh is not null and rownum=1  and t.qyid="+form.getQyid()+" order by id desc";
	                	List<YwRqQyjyxk> list=ywRqQyjyxkDao.find(sql);
	                	jyxk=list.get(0);
	                	jyxkbh=jyxk.getZsbh();
	                }
	            }
	            if(StringUtils.isNotBlank(jyxkbh)){//取经营许可的中间证号
	                jyxkbh=jyxkbh.substring(3,jyxkbh.length());
	            }
	            if(entity!=null){
	                form.setFzr(entity.getFzr());//设置负责人
	            }
            	int maxsslyb=this.maxNumSslyb(form.getId());
            	 int len=3-String.valueOf(maxsslyb+1).length();
            	 String prefixstr="";
                 for(int i=0;i<len;i++){
                	 prefixstr+="0";
                 }
            	String printnum="01-"+jyxkbh+"-"+prefixstr+(maxsslyb+1);
            	entity.setBszsbh(printnum);
            	 if(StringUtils.isBlank(entity.getValiddate())){
            		 entity.setValiddate(this.getJyxkValiddate(form.getId())); 
            	 }
            	 if(entity.getFzdate()==null){
            		 entity.setFzdate(DateUtilsEX.dateFormate(new Date(), "yyyy-MM-dd"));	
  	            	 }
        		 form.setBszsbh(printnum);//供应设施许可证编号
	        }
}
